0

I want to concatenate values from multiple rows into one. I am using COALESCE for this purpose. One of the columns I have is an ID column. When concatenating ID column, values up to 9 are displayed correctly but after nine, asterisk is displayed. Anyone knows why this is? See my code below using COALESCE to concatenate all rows in one:

CREATE TABLE #test
(id int, name varchar(50))
insert into #test
values(1, 'ana'),
(2, 'bob'),
(3, 'steph'),
(4, 'bill'),
(5, 'john'),
(6, 'jose'),
(7, 'kerry'),
(8, 'frank'),
(9, 'noah'),
(10, 'melissa')

--SELECT * FROM #test 

DECLARE @NameAndID VARCHAR(1000)
SELECT @NameAndID = COALESCE(@NameAndID +'; ', '') + CAST(ID AS VARCHAR(1))+'. ' + name
FROM #test 

SELECT @NameAndID
Salman A
  • 262,204
  • 82
  • 430
  • 521
Stephanie
  • 496
  • 7
  • 26
  • 5
    Well, a `VarChar(1)` can only hold a single digit and `10` is clearly two digits. To avoid truncating the result it's returned as `*` – dnoeth Nov 09 '18 at 21:08
  • What is your desired result based on your sample data? – Andrew Nov 09 '18 at 21:08
  • @dnoeth This is it!!! Can't believe I spent so much time on this and couldn't see the length of 1.. – Stephanie Nov 09 '18 at 21:13

2 Answers2

1

You are casting the number to varchar(1) - and any number that have more than a single digit will overflow the one char and therefor will be turned into an asterisks (*).

When casting ints, I find it best to use varchar(11), since this covers the maximum amount of chars that might be needed to display an int.
The int minimum value is -2,147,483,648 - removing the thousands separators it's 10 digits and a minus sign:

-2147483648
123456789 1 (10 is missing in the chars count to make it more clear)

By the way, there are better ways of doing string aggregation in T-Sql.

For versions prior to 2017, use a combination of stuff and for xml path, like this:

SELECT STUFF(
    (
        SELECT '; ' + CAST(id as varchar(11)) + '. ' + name
        FROM #test
        FOR XML PATH('')
    ),1 ,2, '')

For version 2017 or higher, use the built in string_agg function, like this:

SELECT STRING_AGG(CAST(id as varchar(11)) + '. '+ name, '; ')
FROM #Test

for more information, check out this SO post.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • [Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) Be sure to read the link I've edited in and also the link to the relevant documentation in Salman's answer. – Zohar Peled Nov 09 '18 at 21:23
1

The * is an indicator that the result length (was) too short to display. In your example you're trying to fit a two digit number into VARCHAR(1). In this particular case the result is the * instead of throwing an error.

The behavior is described in the docs.

Salman A
  • 262,204
  • 82
  • 430
  • 521