Solved

concat function in sql

Posted on 2014-01-09
10
541 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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 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 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

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.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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