Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2”. I had a great time delivering the session. During the session, we talked about SQL Server 2012 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though, SQL Server is now in moving very fast and have proved many times a better enterprise solution, it does not have some basic functions. I naturally asked him for an example and he suggested CONCAT() which exists in MySQL and Oracle.
The answer is very simple – the equivalent function in SQL Server to CONCAT() is ‘+’ (plus operator without quotes).
Method 1: Concatenating two strings
SELECT 'FirstName' + ' ' + 'LastName' AS FullName
Method 2: Concatenating two Numbers
SELECT CAST(1 AS VARCHAR(10)) + 'R' + CAST(2 AS VARCHAR(10))
Method 3: Concatenating values of table columns
SELECT FirstName + ' ' + LastName
FROM AdventureWorks.Person.Contact
Well, this may look very simple but sometimes it is very difficult to find the information for simple things only.
Do you have any such example which you would like to share with the community?
Watch a quick video relevent to this subject:
[youtube=http://www.youtube.com/watch?v=HbbRpg-tHz4]
Reference: Pinal Dave (https://blog.sqlauthority.com)
47 Comments. Leave new
The following function can be a good example that I wrote
ALTER FUNCTION [dbo].[fnConcatString]
(
@sInputList VARCHAR(8000) — List of delimited items
, @sDelimiter VARCHAR(8000) = ‘,’ –delimiter that separates items
)
RETURNS VARCHAR(4000) –TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
DECLARE @FirstString VARCHAR(100)=”
DECLARE @SecondString VARCHAR(100)
DECLARE @Result VARCHAR(100)
set @sInputList = dbo.udf_TitleCase(@sInputList)
WHILE CHARINDEX(@sDelimiter,@sInputList,0)0
BEGIN
SELECT @sItem = RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
begin
set @FirstString =@FirstString + @sItem
end
END
IF LEN(@sInputList) > 0
begin
SET @SecondString = @sInputList — Put the last item in
end
Set @Result = @FirstString+”+@SecondString
RETURN @Result
END
Hi guyes
please follow the following LOC of my above post
CHARINDEX(@sDelimiter,@sInputList,0) != 0
the less than () has been encoded I guess by the site so that (!=) is not coming you can also use “”
Hi, following is the mysql query
DELETE FROM ATTRIBUTE_INSTANCE WHERE ATTRIBUTE_INSTANCE.ATTRIBUTE_NAME
IN ( SELECT CONCAT(ATTRIBUTE_TEMPLATE.TEMPLATE_ID,’.’,ATTRIBUTE_TEMPLATE.ATTRIBUTE_NAME) FROM
ATTRIBUTE_TEMPLATE, TEMP_ENTITY_TABLE WHERE
ATTRIBUTE_TEMPLATE.TEMPLATE_ID=TEMP_ENTITY_TABLE.ENTITY_ID);
I want to convert it to sql server specific query. please help me
thanks
How to concatinate variable of datatype varchar and function getdate() while alter table query?
just want to ask how to concatenate this one.
(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database= ‘ + @ExcelFile + ‘, ‘select * from [Sheet1$]’) AS A;
Im’ getting errors
just want to ask how to concatenate this one.
(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database= ‘ + @ExcelFile + ‘, ‘select * from [Sheet1$]‘) AS A;
Im’ getting errors
Dear every one,
I find that select @string = @string + columna +columnb from tabelx
will produce memory leak if the tablex is quite big. Is there any way to this ?
I have sql update queries stored in a column like select * from abc and now i want to execute these statements with a where condition. How do i concatenate condition with the select statement in database. i ried using cursor fetching values to variable and then
@condition=@sql+’where 1=1′
MSSQL 2012 now has a concat function.
select f2+’ ‘+f3+’ ‘+f4 as ‘Name’ from [Table1] where ‘name’ in
(select [full name] from [Table2])
how to solve this?? i want to concat three columns and compare result with another column from Table 2. I am using sql server 2008 r2
SELECT f2+’ ‘+f3+’ ‘+f4 AS ‘Name’ FROM Table1
WHERE CONCAT(f2,’ ‘,f3,’ ‘,f4) in
(SELECT fullName FROM Table2)
OR
SELECT TB1.f2+’ ‘+TB1.f3+’ ‘+TB1.f4 AS ‘Name’ FROM Table1 AS TB1
Inner Join Table2 AS TB2 ON TB2.fullName = CONCAT(TB1.f2,’ ‘,TB1.f3,’ ‘,TB1.f4)
2008 does not support concat function
declare @a char (10)
set @a=’01’
print @a
set @a=@a+’02’
print @a
…..it gives 01 both times but when i use ‘declare @a varchar (10)’ then it’s working fine…?
Hello Rahul Bansal!
Datatype char has a fixed size, in your case 10. Your variable @a does not only contain ’01’, it contains also 8 whitespaces, too.
If you now add ’02’ it won’t be added since @a has already the maximum amount of characters it can hold.
The following piece of code will show the “effect”:
declare
@a char (10) = ’01’,
@c char (12) = ’01’
print @a
print replace(@a, space(1), ‘.’) — show the whitespaces in order to make it clear
print @c
set @c = @a + ’02’ — will fit since variable c can hold up to 12 characters.
set @a = cast(@a as char(8)) + ’02’
print @a
print @c
Hope that helps.
Greetings,
Michael
CONCAT function in SQL Server 2012 allows TEXT types to be joined, but theSQL Server 2008 R2 and earlier versions using the ” + ” method does not from my experience. That’s the problem I’ve run into.
SELECT CAST(NULL AS VARCHAR(10)) + ‘R’ + CAST(2 AS VARCHAR(10))
Can you check this result.
I am getting null result.
Because you are using NULL in the CAST function. Anything you add to NULL always gives you NULL result.
SET CONCAT_NULL_YIELDS_NULL OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
I found this solution but it is good to use?
It will change how NULL is perceived in your database. It is upto your database design.