I need to get a comma separated list with quotes and I'm almost at that point but I get a list with commas but a leading space at the start but in between the first quote and the string.

select stuff((select distinct ',' '''' str(bid) '''' from dbo.Companies
where IdCompany in (select substring(ID_COMPANY,1,LEN(ID_COMPANY)-2)  from
sql.Companies where SEGMENT = @Segment and STATE = 'ACTIVE')
FOR XML PATH('')) , 1 , 1 , '' )

I get something like this:

' 500004600',' 500005200',' 500009600',' 500021500'

How do I remove the left space so that I just get:

'500004600','500005200','500009600','500021500'

CodePudding user response：

This is documented on the STR() function

length

Is the total length. This includes decimal point, sign, digits, and spaces. The default is 10.

You have not specified a value so it's spacing it out to 10.

You should instead CAST the number to varchar. An easier option, when you anyway have concatenation, is to use CONCAT which will cast it also.

You should also remove the DISTINCT and use GROUP BY bid, otherwise indexes and other optimizations cannot be used (because of the concatenation).

SELECT STUFF((
    SELECT CONCAT(',''', c.bid, '''')
    FROM dbo.Companies c
    WHERE c.IdCompany in (
        SELECT SUBSTRING(c2.ID_COMPANY, 1, LEN(c2.ID_COMPANY) - 2)
        FROM sql.Companies c2
        WHERE c2.SEGMENT = @Segment
          AND c2.STATE = 'ACTIVE'
    )
    GROUP BY
      c.bid
    FOR XML PATH('')
  ) , 1 , LEN(',') , '' )

As a side point, if you are concatenating strings which may have XML escaping issues, you would need

    FOR XML PATH(''), TYPE
  ).value('text()[1]','varchar(max)') , 1 , LEN(',') , '' )

Obviously, in newer versions of SQL Server, you can just use STRING_AGG

 SELECT STRING_AGG(CONCAT('''', c.bid, ''''), ',')
 FROM dbo.Companies c
 WHERE c.IdCompany in (
     SELECT SUBSTRING(c2.ID_COMPANY, 1, LEN(c2.ID_COMPANY) - 2)
     FROM sql.Companies c2
     WHERE c2.SEGMENT = @Segment
       AND c2.STATE = 'ACTIVE'
 )
 GROUP BY
   c.bid