在as关键字之后如何用单位词替换特征名？

tnkciper 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(407)

我在使用SQLServer2012时面临一个问题：我不能将包含unit word的功能替换为unit only。
所以这个代码返回

'FamilyUnit' as [FamilyUnit]

当我需要的时候

'FamilyUnit' as [Unit]

如果单词包含 Unit ，那么应该是 Unit 只是。
变化将在 AS 关键字。

create table #SplitNumberAndUnitsFinal
(
     DKFeatureName nvarchar(100),
     DisplayOrder int
)

insert into #SplitNumberAndUnitsFinal (DKFeatureName, DisplayOrder)
values ('package', 1), ('packageUnit', 1),
       ('Family', 2), ('FamilyUnit', 2),
       ('parts', 3), ('partsUnit', 3)

DECLARE @Header nvarchar(max) =  
    (SELECT SUBSTRING((SELECT ', ''' + DKFeatureName + ''' AS ['+ DKFeatureName +']' AS [text()]
     FROM #SplitNumberAndUnitsFinal 
     GROUP BY DKFeatureName
     ORDER BY MIN(DisplayOrder)
     FOR XML PATH ('')), 2, 10000) [Columns])

PRINT @Header

预期结果是替换所有包含 Unit 只是 Unit 所以输出应该是：

'package' as [package], 
 'packageUnit' as [Unit], 
 'Family' as [Family], 
 'FamilyUnit' as [Unit],  
 'parts' as [parts], 
 'partsUnit' as [Unit]

sql stored-procedures dynamic-sql tsql sql-server-2012

来源：https://stackoverflow.com/questions/62620127/how-to-replace-feature-name-have-unit-word-by-unit-only-after-as-keyword

1条答案

按热度按时间

tyg4sfes1#

使用 CASE 表达式：

DECLARE @Header nvarchar(max) =  
    (SELECT SUBSTRING((SELECT ', ''' + DKFeatureName + ''' AS '
        + QUOTENAME(CASE WHEN DKFeatureName LIKE '%Unit' THEN 'Unit' ELSE DKFeatureName END) AS [text()]
     FROM #SplitNumberAndUnitsFinal 
     GROUP BY DKFeatureName
     ORDER BY MIN(DisplayOrder)
     FOR XML PATH ('')), 2, 10000) [Columns])

引用标识符更安全 QUOTENAME 函数而不是添加 [] db<>小提琴演示

赞(0）回复(0）举报 2021-08-09

我来回答

在as关键字之后如何用单位词替换特征名？

1条答案

相关问题

热门标签

最新问答