Improve company productivity with a Business Account.Sign Up

x
?
Solved

concat function in sql

Posted on 2014-01-09
10
Medium Priority
?
560 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
  • 4
  • 2
  • 2
  • +1
9 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 72

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 72

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

584 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