1

I need to remove the last character of my subquery but I cannot figure out how to reference my subquery in the subselect as I need the length of it.

The subselect collects all rows and converts them into a single string by appending them with a separator ";".

This is what I need:

(SUBSTRING(
    (SELECT 
                    d1.name +'; '
    FROM   
                    data1 d1 
                    inner join data2 d2
                    on d1.id = d2.id
    WHERE   
                    d1.id = dOfOutterSelect.id 
    ORDER BY
                    d1.CreatedOn FOR XML PATH('') 

    ),
LENGTH(),1)) 

As "Row name",
Improve this question
1
  • if you are on SQL Server 2017+ you can use STRING_AGG function to concatenate rows instead of this construction with FOR XML PATH Commented Dec 5, 2020 at 17:47

2 Answers 2

Reset to default
0

Use the STUFF(...) T-SQL function to strip the last character off. First REVERSE the string from the query results, then use STUFF, then REVERSE the result again.

Like this:

SELECT REVERSE(STUFF(REVERSE('this is my string'), 1, 1, ''))

The result looks like:

Results
this is my strin

Here's the Microsoft Docs pages for STUFF and REVERSE.

Actually, looking at your query more closely, you can refine it slightly to eliminate the use of the REVERSE function. Take the example below, where I'm concatenating the names of all the columns in the database with a semi-colon separator.

SELECT STUFF((
    SELECT N'; ' + c.name
    FROM sys.columns c
    FOR XML PATH (N'')
    ), 1, 2, N'');

I add the semi-colon at the start of the string, then simply use STUFF to replace the first 2 characters with "nothing".

So the results look like:

Results
Column1; Column2; Column3

Newer versions of SQL Server (2017+) support the use of the STRING_AGG aggregate which can significantly simplify this kind of query. To get the same output as my query above, using STRING_AGG, you'd write it like this:

SELECT STRING_AGG(CONVERT(nvarchar(max), c.name), N'; ')
FROM sys.columns c;

There is CONVERT(nvarchar(max), ...) wrapped around the c.name column to coerce the STRING_AGG function into using a string of sufficient length to contain all the column names in my database. If you leave that out, SQL Server reports an error:

Msg 9829, Level 16, State 1, Line 10
STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

Improve this answer
1
  • The N prefix on a string indicates to SQL Server that the string is of type nchar or nvarchar, which prevents an implicit conversion when concatenating to nvarchar/nchar/sysname columns (in this case, sys.name is a sysname, which is a synonym for nvarchar(128)). Also, see Write differences between varchar and nvarchar. Commented Dec 7, 2020 at 13:13
0

Unfortunately I have another problem. This does not work when "; " is mixed with integer. I tried the following without success

(SELECT STUFF(
    (SELECT 
                    '; ' +  CONVERT(INT,
                            CASE
                            WHEN IsNumeric(CONVERT(VARCHAR(12), d1.numbericData)) = 1 THEN CONVERT(VARCHAR(12),c.numbericData) 
                            ELSE 0 END)  
    FROM   
                    data1 d1 
                    inner join data2 d2
                    on d1.id = d2.id
    WHERE   
                    d1.id = dOfOutterSelect.id 
    ORDER BY
                    d1.CreatedOn FOR XML PATH('') 


), 1, 2, N''))
´´´
Improve this answer
3
  • Found a solution: CONCAT('; ', d1.data) Commented Dec 7, 2020 at 10:09
  • Please do not add details or another question in the "Your Answer" box. Either add these details to your qustion at the top via the edit function, or ask an entirely new question via dba.stackexchange.com/questions/ask Commented Dec 7, 2020 at 13:15
  • The reason this "doesn't work" is because you're attempting to append an int to a string. This will result in an error reported by SQL Server. Commented Dec 7, 2020 at 13:15

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.