[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

concat function in sql

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
sqldba2013
Asked:
sqldba2013
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
Dale BurrellDirectorCommented:
update T1 set C1 = (Sql1) + (Sql2)
0
 
Louis01Commented:
Here's one way:
declare @res varchar(max);
set @res = (select '1') + ';' + (select '2');
select @res;

Open in new window

0
 
Louis01Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

Open in new window

0
 
QlemoDeveloperCommented:
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
 
Louis01Commented:
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
 
sqldba2013Author Commented:
--
0
 
QlemoDeveloperCommented:
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
 
Louis01Commented:
I agree
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now