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
Sir generally in when we concat to field we should use ISNULL() because if one of them value is NULL then result will show NULL.
If you want catch a null value is correct but not all time, ‘concat’ says something to you??
concat means union of two or more words, read correctly and then replay
Vinod actually raised a very important point. Most of the time users are using concat to concatonate 2 or more FIELDS. If any field contains a NULL value the concat will fail.
Your reply is pretty embarassing TBH. Why when concatonating fields would you only want to capture null values ‘some of the time’? Sure all of the time is better as you know it will work every time!! :-)
Split function is not present in SQL Server 2005
hi sumit for split string you have to use function which is
CREATE FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(8000) — List of delimited items
, @sDelimiter VARCHAR(8000) = ‘,’ — delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
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
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList — Put the last item in
RETURN
END
I know this function but I was talking about built in split fucntion
What I think SQL needs is a native concat aggregate function. It can be done with .NET, but it really should just be setup like sum, except for strings.
I always wonder why no one had add LIMIT() to the SQL Server like in MySQL.
I the past (< SQL Server 2005) some wicked nested selects with TOP were necessary while from SQL Server 2005 on you can use row_number() to achieve a similar effect.
Greetings,
Michael
“Well, this may look very simple but sometime it is very difficult to find the information for simple things only”
Really? Just googling “Concat SQL Server” gave me a whole page of results, all of which mentioned using the “+” option.
When concatenating more then two string values ,CONCAT() function is more difficult to use as compared to || signs in oracle or + sign in Sql Server.
Like SELECT CONCAT(CONCAT(‘firt value’,’ second value’),’third value’) AS result
But in Sql Server its so simple
SELECT ‘firt value’+’ second value’+’ third value’
concat function takes n-number of arguments, so you don’t need to nest concats, ie this is valid:
CONCAT(‘first value’,’ second value’,’third value’)
I use method 3 all the time.
Hi,
I have been using the ‘+’ operator for a long time in SQL Server for concatenation purposes. One of recent comparisions i had to do was for the CONNECT BY clause available in ORACLE. I used CTE’s to get a similar effect as the CONNECT BY.
Estou fazendo uma função e esbarrei no problema de não conseguir concatenar no from ou nas colunas….. Alguém pode me ajudar? Nas linhas destacadas estão o que quero fazer…
alter function fcn_consultaTitulos(@result varchar(15), @nf varchar(9), @serie varchar(2), @emissao varchar(15), @cliLoja varchar(20), @parcela char(1), @empresa varchar(2))
returns varchar(20)
as begin
declare @resultado varchar(20);
declare @tabela varchar(10);
set @tabela = ‘SE1’+@empresa+’0′;
if(@result=’valor’)
begin
set @resultado =
(SELECT top 1 E1_VALOR
FROM @tabela SE1
WHERE SE1.D_E_L_E_T_ ‘*’ AND SE1.E1_FILIAL=’00’
AND SE1.E1_NUM =@nf
AND SE1.E1_SERIE =@serie
AND SE1.E1_EMISSAO=@emissao
AND SE1.E1_CLIENTE+SE1.E1_LOJA =@cliLoja
AND E1_PARCELA = @parcela
ORDER BY SE1.E1_NUM, SE1.E1_SERIE,SE1.E1_PARCELA);
end
else
if(@result=’dataVenc’)
begin
set @resultado =
(SELECT top 1 E1_VENCREA
FROM SE1+@empresa+0 SE1
WHERE SE1.D_E_L_E_T_ ‘*’ AND SE1.E1_FILIAL=’00’
AND SE1.E1_NUM =@nf
AND SE1.E1_SERIE =@serie
AND SE1.E1_EMISSAO=@emissao
AND SE1.E1_CLIENTE+SE1.E1_LOJA =@cliLoja
AND E1_PARCELA = @parcela
ORDER BY SE1.E1_NUM, SE1.E1_SERIE,SE1.E1_PARCELA);
end
return @resultado
end
Desde já agradeço.
Jardel
Sorry pinaldave, I writed in portuguese…
The question is that I need pass a param to set the number of the company to table, e.g:
(company = 01)
create function dbo.fcn_consultaTitulos(@companyvarchar(2))
returns varchar(20)
as begin
declare @resultado varchar(20);
if(@result=’valor’)
begin
set @resultado =
(SELECT top 1 E1_VALOR
FROM SE1+{@company}+0 SE1
WHERE ……
A funny story on concatenation in ANSI/ISO SQL. The correct syntax is || and not +. Overloading the + has been a problem for decades in T-SQL.
The “pipe” symbol was submitted to ANSI X2H2 by Phil Shaw of IBM. Phil had been on the PL/I development team and borrowed this syntax.
When we asked him if PL/I had any other good ideas we should look at for SQL, he went into a rant about how PL/I was the worst language ever designed. :)
Suppose, we want to concatenate output returned in one column by query, what would be the best way?
We have been using a CLR for such concatenation.
Ex:
SELECT master.dbo.CONCATENATE(name)
FROM master.sys.tables
———
spt_fallback_db,spt_fallback_dev,spt_fallback_usg,spt_monitor,spt_values,MSreplication_options
Hi Pinal, how we can concatenate number and string?
Example: If i want to print as filesize and MB next to file size like 120 MB for the below query.. how to do that?
select (size*8) as FileSize from sys.database_files
Use this code
select cast((size*8) as varchar(10))+’ MB’ as FileSize from sys.database_files
When doing yours and either column is null it just shows null for me, so I did:
Select Case When (ForeName + ‘ ‘ + SurName) is null then (Select Case when (ForeName) is null then (SurName) else (ForeName) end) else (ForeName + ‘ ‘ + SurName) end as Fullname from customers
Of course..Sometime we break head for simple queries..:) But they help you learn lot coz u keep exploring..:)
hm, what if neither ‘+’ nor ‘Concat()’ are supported by SQL Server 2008 R2 using CF9? well, let me correct myself. ‘+’ is officially supported, however it can’t do the job because I’m trying to concat a text col with some user-updated content, and so the types don’t line up. well, so says the error msg returned from SQL. See below for exactly what happens.
Anyone know how to get around this?
When I do THIS:
update tblNotes set sNotesAppend = sNotesAppend + ‘(user-updated content)’
I got the error:
ErrorCode = “402”
Message = “[Macromedia][SQLServer JDBC Driver][SQLServer]The data types text and varchar are incompatible in the add operator.”
SQLState = “HY000”
So I turned to Googs which led me here and I see you mention Concat(), so I tried the same. However when I do it, when I do THIS instead:
update tblNotes set sNotesAppend = Concat(sNotesAppend, Char(13), Char(10), Char(13), Char(10), GetDate(), Char(13), Char(10), ‘(user-updated content)’)
I get the error:
ErrorCode = “195”
Message = “[Macromedia][SQLServer JDBC Driver][SQLServer]’Concat’ is not a recognized built-in function name.”
SQLState = “HY000”
Fantastic! If I keep going this way I can waste the whole day instead of just parts of it! AWESOME!~
Maybe cast the varchar as type text somehow?
Anyway I could theoretically do a workaround where I handle in CF9 what SQL is giving me trouble with, but that’s a seriously huge workaround, cos:
As the solution above provides, I can update many rows at once by doing it “where NotesID in (” & (csv list of IDs) & “)”. But the meanderingly long workaround would be, select each record, append the value in CF, then update that one record; it’d forbid the multiple updates, and hence be a lot more work. I’d like a simple elegant solution if possible, if SQL has one that is. Thanks for your time but an additional bonus thanks if anyone happens to know it:)
OK also, not to come across like a complete idiot, I must point out that I later realized that what you were saying above was that Concat() does not exist in 2008 R2 and that the ‘+’ operator is the proper method. Regardless though, my question remains valid how one can properly (reliably) use ‘+’ to concatenate various entities with each other, especially a column’s current value for that record, dynamically. Thanks again
Thanks buddy, I really appreciate the time you take to post these tips.
I need to use something like the concat function in a subquery. The result set is a variable number of strings that should be concatenate to a single string. Because of the dynamic number of rows ‘+’ doesn’t work. So I am wondering why concat does not work although it is a documented function in Transact- SQL. Can anybody help?