当生成select语句时，它会给我奇怪的文本作为&gt；何时选择数据？

我在sql server 2012上工作，我面临一个问题：从@body中选择时会生成奇怪的文本
“作为” > "
如何删除，为什么会生成？
此签名为 > 选择数据时为我解决问题
从表中选择@body
在'；'附近它给我错误的语法
样本数据：

create table #FinalTable
(
PART_ID nvarchar(50) ,
CompanyName  nvarchar(50),
PartNumber nvarchar(50),
DKFeatureName nvarchar(100),
value nvarchar(50),
StatusId  int,
DisplayOrder  int,
splitFlag bit
)

insert into #FinalTable(
PART_ID  ,
CompanyName ,
PartNumber ,
DKFeatureName  ,
value   ,
StatusId  ,
DisplayOrder ,
splitFlag)
values
('1222','Honda','silicon','package','15.50Am',2,5,0),
('1900','MERCEIS','GLASS','family','90.00Am',2,2,1),--have column per Unit on @Header because FlagAllow=1
('5000','TOYOTA','alominia','source','70.20kg',2,1,0),
('8000','MACDA','motor','parametric','50.40kg',2,3,1),--have column per Unit on @Header because FlagAllow=1
('8900','JEB','mirror','noparametric','75.35kg',2,4,0)

DECLARE @Body NVARCHAR(MAX)
SELECT
    @Body = STUFF(
        (                                                                                                           
        SELECT ', ' +  case when A.splitFlag = 1 and a.value<> '-' and (a.Value is not null)  then 'LEFT(' + QUOTENAME (A.DKFeatureName) + ',PATINDEX(''%[^0-9.]%'',' + QUOTENAME (A.DKFeatureName)  + '+ ' + ''' ''' + ')-1) as ['+A.DKFeatureName+'],(CASE WHEN PATINDEX(''%[^0-9.]%'', '+ A.DKFeatureName + ') > 0 THEN RIGHT('+ QUOTENAME (A.DKFeatureName) +',LEN('+ QUOTENAME (A.DKFeatureName) +') - PATINDEX(''%[^0-9.]%'','+ QUOTENAME (A.DKFeatureName) +')+1)) ELSE NULL END) as  ['+A.DKFeatureName +'Units'+']' else quotename(A.DKFeatureName) end
            FROM #FinalTable A 
            where StatusId=2
                 ORDER BY A.DisplayOrder
            FOR XML PATH ('')
        ),1,2,''
    )
   print @Body

字符串生成如下：

[Series], [Cable Type], LEFT([Impedance],PATINDEX('%[^0-9.]%',[Impedance]+ ' ')-1) as [Impedance],(CASE WHEN PATINDEX('%[^0-9.]%', Impedance) &gt; 0 THEN RIGHT([Impedance],LEN([Impedance]) - PATINDEX('%[^0-9.]%',[Impedance])+1)) ELSE NULL END) as  [ImpedanceUnits], LEFT([Frequency - Max],PATINDEX('%[^0-9.]%',[Frequency - Max]+ ' ')-1) as [Frequency - Max],(CASE WHEN PATINDEX('%[^0-9.]%', Frequency - Max) &gt; 0 THEN RIGHT([Frequency - Max],LEN([Frequency - Max]) - PATINDEX('%[^0-9.]%',[Frequency - Max])+1)) ELSE NULL END) as  [Frequency - MaxUnits], [Color]

预期结果gt必须为>，并删除分号和&

[Series], [Cable Type], LEFT([Impedance],PATINDEX('%[^0-9.]%',[Impedance]+ ' ')-1) as [Impedance],(CASE WHEN PATINDEX('%[^0-9.]%', Impedance) > 0 THEN RIGHT([Impedance],LEN([Impedance]) - PATINDEX('%[^0-9.]%',[Impedance])+1)) ELSE NULL END) as  [ImpedanceUnits], LEFT([Frequency - Max],PATINDEX('%[^0-9.]%',[Frequency - Max]+ ' ')-1) as [Frequency - Max],(CASE WHEN PATINDEX('%[^0-9.]%', Frequency - Max) > 0 THEN RIGHT([Frequency - Max],LEN([Frequency - Max]) - PATINDEX('%[^0-9.]%',[Frequency - Max])+1)) ELSE NULL END) as  [Frequency - MaxUnits], [Color]

我需要显示gt&as>0

你需要使用 TYPE 关键字强制保留字符，然后在生成的xml中提取文本的值，而不是使用 value :

DECLARE @Body nvarchar(MAX);
SELECT @Body = STUFF((SELECT ', ' + CASE
                                         WHEN A.splitFlag = 1
                                          AND A.value <> '-'
                                          AND (A.Value IS NOT NULL) THEN 'LEFT(' + QUOTENAME(A.DKFeatureName) + ',PATINDEX(''%[^0-9.]%'',' + QUOTENAME(A.DKFeatureName) + '+ ' + ''' ''' + ')-1) as [' + A.DKFeatureName + '],(CASE WHEN PATINDEX(''%[^0-9.]%'', ' + A.DKFeatureName + ') > 0 THEN RIGHT(' + QUOTENAME(A.DKFeatureName) + ',LEN(' + QUOTENAME(A.DKFeatureName) + ') - PATINDEX(''%[^0-9.]%'',' + QUOTENAME(A.DKFeatureName) + ')+1)) ELSE NULL END) as  [' + A.DKFeatureName + 'Units' + ']'
                                         ELSE QUOTENAME(A.DKFeatureName)
                                    END
                      FROM #FinalTable A
                      WHERE StatusId = 2
                      ORDER BY A.DisplayOrder
                     FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,2,'');
PRINT @Body;

当生成select语句时，它会给我奇怪的文本作为&gt；何时选择数据？

1条答案

相关问题

热门标签

最新问答