Concatenate strings

Question

Post reply

Concatenate strings

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
June 21, 2016 at 4:41 pm

#314652

In my sample code, at the end i want @database to be A,B,C. I am not able to concatenate them.
DECLARE @database NVARCHAR(100) ,
@database2 NVARCHAR(100) ,
@database3 NVARCHAR(100)
DECLARE @dbs TABLE ( DBname NVARCHAR(100) )
INSERT INTO @dbs
VALUES ( 'A' ),
( 'B' )
,
( 'C' )
DECLARE databases CURSOR
FOR
SELECT DBname
FROM @dbs
OPEN databases
FETCH NEXT FROM databases INTO @database
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
FETCH NEXT FROM databases INTO @database
END
CLOSE databases
DEALLOCATE databases

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply

santiagoc93 Valued Member Points: 66 More actions · Answer 1

SELECT

COALESCE(

STUFF(

(SELECT ',' + CAST(dbs AS VARCHAR(10)) AS [text()]

FROM #l

ORDER BY dbs

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),

1, 1, ''),

'') AS letters

FROM #l as c;

durga.palepu Hall of Fame Points: 3530 More actions · Answer 2

additional information:

SQL Server 2016 offers new feature, STRING_SPLIT() which returns delimited strings as records.

e.g.

SELECT STRING_SPLIT('A,B,C',',')

Output:

A

B

C

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

durga.palepu (6/22/2016)
additional information:
SQL Server 2016 offers new feature, STRING_SPLIT() which returns delimited strings as records.
e.g.
SELECT STRING_SPLIT('A,B,C',',')
Output:
A
B
C

While that is true it has no relevance in this question. The OP already has each value in a separate row. They are trying to put them together into a single value.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

durga.palepu Hall of Fame Points: 3530 More actions · Answer 4

I knew that, I've provided additional useful information.

durga.palepu Hall of Fame Points: 3530 More actions · Answer 5

I clearly mentioned that its additional information.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 6

May be i didn't explain it correctly. Basically the curson will loop through list of databases and at the end the value in the variable @database should be A, B ,C.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

No, I think you explained it quite well. But you don't need a cursor to do this. Does what santiagoc93 posted not work for you?

John

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Here is the concept posted by santiagoc93 using your sample data.

DECLARE @dbs TABLE ( DBname NVARCHAR(100) )

INSERT INTO @dbs

VALUES ( 'A' ),

( 'B' ),

( 'C' )

SELECT distinct

STUFF((SELECT ',' + CAST(DBname AS VARCHAR(10))

FROM @dbs

ORDER BY DBname

FOR XML PATH(''))

, 1, 1, '') AS DBNames

FROM @dbs

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 9

Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

curious_sqldba (6/22/2016)
Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.

Do you know how to use a select statement to populate a variable? It is no different here.

However, I have a feeling that you are about to embark down a path of no return. The fact that you are creating a delimited list in a variable leads to believe you are hoping to use that in where clauses. If that is the case you are going to end up in another pickle....variables don't work like that.

Perhaps you can explain what you are really trying to accomplish and we can help you find a better approach.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 11

Sean Lange (6/22/2016)
curious_sqldba (6/22/2016)
Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.
Do you know how to use a select statement to populate a variable? It is no different here.
However, I have a feeling that you are about to embark down a path of no return. The fact that you are creating a delimited list in a variable leads to believe you are hoping to use that in where clauses. If that is the case you are going to end up in another pickle....variables don't work like that.
Perhaps you can explain what you are really trying to accomplish and we can help you find a better approach.

You are right i did horrible job in explaining my question :(.

The above query works but i went with a different solution.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

curious_sqldba (6/22/2016)
Sean Lange (6/22/2016)
curious_sqldba (6/22/2016)
Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.
Do you know how to use a select statement to populate a variable? It is no different here.
However, I have a feeling that you are about to embark down a path of no return. The fact that you are creating a delimited list in a variable leads to believe you are hoping to use that in where clauses. If that is the case you are going to end up in another pickle....variables don't work like that.
Perhaps you can explain what you are really trying to accomplish and we can help you find a better approach.
You are right i did horrible job in explaining my question :(.
The above query works but i went with a different solution.

Please post your solution.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

curious_sqldba (6/22/2016)
Sean Lange (6/22/2016)
curious_sqldba (6/22/2016)
Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.
Do you know how to use a select statement to populate a variable? It is no different here.
However, I have a feeling that you are about to embark down a path of no return. The fact that you are creating a delimited list in a variable leads to believe you are hoping to use that in where clauses. If that is the case you are going to end up in another pickle....variables don't work like that.
Perhaps you can explain what you are really trying to accomplish and we can help you find a better approach.
You are right i did horrible job in explaining my question :(.
The above query works but i went with a different solution.

Two way street here... what is the solution that you did go for? It might help US in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Muruthoti Vijaya Kumar SSC Enthusiast Points: 108 More actions · Answer 14

we can do this with COALESCE function also

DECLARE @dbs TABLE ( DBname NVARCHAR(100) )

DECLARE @txt varchar(50)

INSERT INTO @dbs

VALUES ( 'A' ),

( 'B' ),

( 'C' )

select @txt=COALESCE(@txt+',','')+DBname from @dbs

select @txt