Solved

concat function in sql

Posted on 2014-01-09
10
536 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 125 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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 69

Accepted Solution

by:
Qlemo earned 125 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 69

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

749 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