1

Is there a way that I can replace a substring in SQL server while remaining 'aware' of what I'm replacing so that I can then re-insert that content?

I am building a string of column names to pivot on, and these column names need to be aggregated on in the query.

For example, I have this list of columns as an nvarchar(max) string:

[A], [B], [C]

And I would like to transform this string to look like this:

sum([A]) [A], sum([B]) [B], sum([C]) [C], 

I have just found the stuff function but I don't know how to achieve what I want yet.

But I get the feeling that I can use it to insert substrings instead of using replacing substrings.

Looking at the stuff function:

STUFF( source_string, start, length, add_string )

How do you insert into multiple locations at once?

My current solution is this:

declare @cols nvarchar(max);
declare @colsAggregate nvarchar(max);
declare @sql nvarchar(max);

select @cols = stuff((
    select distinct ',' + quotename(sCode)
    from EntityList EL
    where EL.bActive = 1 for xml path(''), type).value('.', 'nvarchar(max)'),
1, 1, '');

select @colsAggregate = stuff((
    select distinct ', max(' + quotename(sCode) + ') ' + quotename(sCode)
    from EntityList EL
    where EL.bActive = 1 for xml path(''), type).value('.', 'nvarchar(max)'),
1, 1, '');
2
  • Why is it so important for your solution to use STUFF? Can you make the real requirement more clear?
    – Joe Obbish
    Dec 19, 2017 at 0:27
  • 1
    usually I'd say go and use the REPLACE (and not stuff) if you want to replace something. Sadly it does not supports RegEx, what you'd need to insert (parts) of the replaced stuff. You could have a look onto stackoverflow.com/questions/21378193/… for suggestions that works with PATINDEX to do similar tasks. Dec 19, 2017 at 8:24

1 Answer 1

4

You need to get it as rows before you can use STUFF.

DECLARE @rows nvarchar(max) = '[A],[B],[C]';
DECLARE @cols nvarchar(max);

SELECT LTRIM(RTRIM(j.value)) FROM STRING_SPLIT(@rows, ',') j

SET @cols = STUFF((SELECT ', ' + (col) 
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,2,'');

SELECT @cols;

SET @cols = STUFF((SELECT ', ' + ('SUM(' + col + ')') 
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,2,'');

SELECT @cols;

SET @cols = STUFF((SELECT ', ' + ('SUM(' + col + ') ' + col) 
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,2,'');

SELECT @cols;
GO
| (No column name) |
| :--------------- |
| [A]              |
| [B]              |
| [C]              |

| (No column name) |
| :--------------- |
| [A], [B], [C]    |

| (No column name)             |
| :--------------------------- |
| SUM([A]), SUM([B]), SUM([C]) |

| (No column name)                         |
| :--------------------------------------- |
| SUM([A]) [A], SUM([B]) [B], SUM([C]) [C] |

dbfiddle here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.