?
Solved

concat function in sql

Posted on 2014-01-09
10
Medium Priority
?
547 Views
Last Modified: 2014-01-12
Please advise how to concat/merge two sql statements results in UPDATE clause with ; symbol.

For ex:,
SQL1:
SELECT [UserInfo].[Name] FROM dbo.[UserInfo] with(nolock) WHERE [Another_DB].[ACR] = [UserInfo].[ID]

Output of above query is XXX.

SQL2:
SELECT [UserInfo].[Name] FROM dbo.[UserInfo] with(nolock) WHERE [Another_DB].[CSR] = [UserInfo].[ID]

Output of above query is YYY.

Update T1
set Col1={SQL1 concat SQL2}

Final results of T1 (required output):
Col1
=====
XXX;YYY
0
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39767389
update T1 set C1 = (Sql1) + (Sql2)
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39767405
Here's one way:
declare @res varchar(max);
set @res = (select '1') + ';' + (select '2');
select @res;

Open in new window

0
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 500 total points
ID: 39767408
Here's another:
declare @res1 varchar(max);
declare @res2 varchar(max);
declare @res_final varchar(max);

set @res1 = (select '1'); 
set @res2 = (select '2'); 
set @res_final = @res1 + ';' + @res2;

select @res_final;
--so
update myTable set myCol = @res_final;

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39767413
Or even cut out all the variables...
update T1 set C1 = (select '1') + (select '2')

Open in new window

0
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39767431
You'll have to make sure the results are strings (varchar). If numeric, the result will be different ;-). And the result may only be one single value for each row of the table to update, but that's the case here.

Just concatting the results with + works, as mentioned above, but we should not forget to add a semicolon:
Update T1
set Col1= 
   (SELECT [UserInfo].[Name] FROM dbo.[UserInfo] with(nolock) WHERE [Another_DB].[ACR] = [UserInfo].[ID])
   +  ';'  +
   (SELECT [UserInfo].[Name] FROM dbo.[UserInfo] with(nolock) WHERE [Another_DB].[CSR] = [UserInfo].[ID])
;

Open in new window

0
 
LVL 11

Expert Comment

by:Louis01
ID: 39767443
Qlemo is quite right about the addition of varchar v.s. numeric (except if there is a ';' between, then you'll get a conversion failure).
If you have numeric results: To get around the conversion failure, use the convert / cast function
0
 

Author Comment

by:sqldba2013
ID: 39767607
--
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39767808
Sorry, but I don't agree to the closure.
a) http:#a39767405 isn't showing the solution. It is rather http:#a39767408 .
b) My post http:#a39767431 is on the point, and contains all data you provided, while http:#a39767408 is very generic (and long-winded).

I would have expected at least an equal split.
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39767826
I agree
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question