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, '');
  • Why is it so important for your solution to use STUFF? Can you make the real requirement more clear? – Joe Obbish Dec 19 '17 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. – Thomas Franz Dec 19 '17 at 8:24
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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