Delete ; symbol at string end (SQL)

Question

I have SQL script that gets roles and return string with list of roles

Here is part of code

, ISNULL(SUBSTRING(
                                  (
                                      SELECT  ECBR.RoleName + ';' AS [text()]
                                      FROM [External].[vw_ExecutiveChangeByRole] ECBR
                                      WHERE ECBR.VendorExecutiveChangeId = ECH.VendorExecutiveChangeId
                                        AND (ECBR.IsCurrentOrPrior IS NULL OR ECBR.IsCurrentOrPrior = 1)
                                        AND ECBR.RecognizedWith = 'ML'
                                      ORDER BY ECBR.RoleId
                                      FOR XML PATH ('')
                                  ), 1, 1000), '')                                   AS RecognizedCurrentRoles

And now showing like VP;Controller;

How I can make this string without last;, just VP;Controller

Don't add it at the end, add it to the start and remove the first character with `STUFF`. That is *far* easier. — Thom A, Dec 15 '21 at 14:56
See, for example, in this [question](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-before-2017). — Thom A, Dec 15 '21 at 14:57
What version of SQL Server are you using? Recent versions have [a `STRING_AGG` function](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) which saves a lot of messing around! — IMSoP, Dec 15 '21 at 14:57
All supported SQL Server versions (ie SQL Server 2017 and later) have a functions for this, `STRING_AGG`. You can replace all this code with `STRING_AGG(';',ECBR.RoleName)`. — Panagiotis Kanavos, Dec 15 '21 at 15:30
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question, no images. — Yitzhak Khabinsky, Dec 15 '21 at 15:55

score 3 · Answer 1 · edited Dec 16 '21 at 12:08

It depends on your version of SQL Server.
If you have a version 2017 or higher than this is very easy, see this example

let's first build some sample data

declare @t table (groupid int, name varchar(50))

insert into @t (GroupID, name)
values (1, 'VP'), (1, 'Controller'), (2,'A'), (2,NULL), (2,'whatever');

the query for sql server 2017 or higher

select  t.groupid,
        string_agg(t.name, ';') as name
from @t t
group by t.groupid

the result is

groupid name
------- ----
1       VP;Controller
2       A;whatever

On older versions you need to select for XLM Path and then use STUFF to get rid of the extra ;

select t.groupid,

       ( select ';' + t2.name
         from   @t t2 
         where  t2.groupid = t.groupid
         for XML PATH('')
       ) as without_stuff,

       stuff( ( select ';' + t2.name
                from   @t t2 
                where  t2.groupid = t.groupid
                for XML PATH('')
              ),
              1, 1, '') as with_stuff


from   @t t
group by t.groupid

the result is

groupid   without_stuff   with_stuff
-------   -------------   ----------
1         ;VP;Controller  VP;Controller
2         ;A;whatever     A;whatever

EDIT

As commented by @Charlieface the XLM PATH could return unexpected results when the character is a special XML character, for example <

lets look at this example

select t.groupid,
       ( select '<' + t2.name
         from   @t t2 
         where  t2.groupid = t.groupid
         for XML PATH('')
       ) as without_stuff
from   @t t
group by t.groupid

it will return this result

groupid  without_stuff
-------  -------------
1        &lt;VP&lt;Controller
2        &lt;A&lt;whatever

I can imagine that this is not what you expected.
To solve this, alter the query like this

select t.groupid,
       (select '<' + t2.name
          from   @t t2 
          where  t2.groupid = t.groupid
          for XML PATH(''), TYPE
       ).value('text()[1]','nvarchar(max)') as without_stuff
from   @t t
group by t.groupid

Now the result will be more what you want

groupid  without_stuff
-------  -------------
1        <vp<Controller
2        <A<whatever

Correct syntax (to unescape XML symbols correctly) is `for XML PATH(''), TYPE ).value('text()[1]','nvarchar(max)')` — Charlieface, Dec 16 '21 at 09:51
@Charlieface I tried your suggestion, but to be honest I don't see any difference in the result. What does this do exact ? — GuidoG, Dec 16 '21 at 10:31
You would only see a difference if you had XML special characters i your text such as `?` or `<`, see https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e172b3e00680e2365cac2f6a698abc95 for example. You never know when someone might put such characters into the text — Charlieface, Dec 16 '21 at 10:55
@Charlieface I edited my answer and added your comments, thanks — GuidoG, Dec 16 '21 at 12:03

Delete ; symbol at string end (SQL)

1 Answers1

Linked