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