Pfthb

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

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.

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.