How to replace Feature Name have Unit word by Unit Only After as Keyword?

I work with SQL Server 2012 and face an issue: I can’t replace Features contain Unit word to be Unit only.

So this code returns

'FamilyUnit' as [FamilyUnit]

while I need it to be

 'FamilyUnit' as [Unit]

Meaning word after as keyword if it contains Unit, then it should be Unit only.

Change will be after the AS keyword.

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

Expected result is to replace all words that contain Unit with just Unit only, so the output should be:

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

Answer

Using CASE expression:

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])

For quoting identifiers is safer to use QUOTENAME function instead of adding []

db<>fiddle demo

Advertisement

Answer