How to Concatenate Text in a Query in SQL Server

How do I concatenate text in a query in sql server?

The only way would be to convert your text field into an nvarchar field.

Select Cast(notes as nvarchar(4000)) + 'SomeText'
From NotesTable a

Otherwise, I suggest doing the concatenation in your application.

How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

SQL Query - Concatenating Results into One String

If you're on SQL Server 2005 or up, you can use this FOR XML PATH & STUFF trick:

DECLARE @CodeNameString varchar(100)

SELECT 
   @CodeNameString = STUFF( (SELECT ',' + CodeName 
                             FROM dbo.AccountCodes 
                             ORDER BY Sort
                             FOR XML PATH('')), 
                            1, 1, '')

The FOR XML PATH('') basically concatenates your strings together into one, long XML result (something like ,code1,code2,code3 etc.) and the STUFF puts a "nothing" character at the first character, e.g. wipes out the "superfluous" first comma, to give you the result you're probably looking for.

UPDATE: OK - I understand the comments - if your text in the database table already contains characters like <, > or &, then my current solution will in fact encode those into <, >, and &.

If you have a problem with that XML encoding - then yes, you must look at the solution proposed by @KM which works for those characters, too. One word of warning from me: this approach is a lot more resource and processing intensive - just so you know.

How to concatenate in SQL Server

To concatenate strings in SQL Server you can simply use the + operator.

Note that if one of the substrings is null then the entire concatenated string will become null as well. therefor, use COALESCE if you need a result even if one substring is null.

select certificateDuration,
       ' DurationType = '+ 
       COALESCE(case
                     when certificateDurationType = 0 then 'Day' 
                     when certificateDurationType = 1 then 'Month'
                     when certificateDurationType = 2 then 'Year'
                     end, '') As DurationType 
from Scientific_Certification

Note: I've used coalesce on your case clause since you have no default behavior (specified by else). this means that if certificateDurationType is not 0, 1 or 2 the case statement will return null.

How to concatenate variables into SQL strings

You can accomplish this (if I understand what you are trying to do) using dynamic SQL.

The trick is that you need to create a string containing the SQL statement. That's because the tablename has to specified in the actual SQL text, when you execute the statement. The table references and column references can't be supplied as parameters, those have to appear in the SQL text.

So you can use something like this approach:

SET @stmt = 'INSERT INTO @tmpTbl1 SELECT ' + @KeyValue 
    + ' AS fld1 FROM tbl' + @KeyValue

EXEC (@stmt)

First, we create a SQL statement as a string. Given a @KeyValue of 'Foo', that would create a string containing:

'INSERT INTO @tmpTbl1 SELECT Foo AS fld1 FROM tblFoo'

At this point, it's just a string. But we can execute the contents of the string, as a dynamic SQL statement, using EXECUTE (or EXEC for short).

The old-school sp_executesql procedure is an alternative to EXEC, another way to execute dymamic SQL, which also allows you to pass parameters, rather than specifying all values as literals in the text of the statement.

---

FOLLOWUP

EBarr points out (correctly and importantly) that this approach is susceptible to SQL Injection.

Consider what would happen if @KeyValue contained the string:

'1 AS foo; DROP TABLE students; -- '

The string we would produce as a SQL statement would be:

'INSERT INTO @tmpTbl1 SELECT 1 AS foo; DROP TABLE students; -- AS fld1 ...'

When we EXECUTE that string as a SQL statement:

INSERT INTO @tmpTbl1 SELECT 1 AS foo;
DROP TABLE students;
-- AS fld1 FROM tbl1 AS foo; DROP ...

And it's not just a DROP TABLE that could be injected. Any SQL could be injected, and it might be much more subtle and even more nefarious. (The first attacks can be attempts to retreive information about tables and columns, followed by attempts to retrieve data (email addresses, account numbers, etc.)

One way to address this vulnerability is to validate the contents of @KeyValue, say it should contain only alphabetic and numeric characters (e.g. check for any characters not in those ranges using LIKE '%[^A-Za-z0-9]%'. If an illegal character is found, then reject the value, and exit without executing any SQL.

SQL Server - Adding a string to a text column (concat equivalent)

like said before best would be to set datatype of the column to nvarchar(max), but if that's not possible you can do the following using cast or convert:

-- create a test table 
create table test (
    a text
) 
-- insert test value
insert into test (a) values ('this is a text')
-- the following does not work !!!
update test set a = a + ' and a new text added'
-- but this way it works: 
update test set a = cast ( a as nvarchar(max))  + cast (' and a new text added' as nvarchar(max) )
-- test result
select * from test
-- column a contains:
this is a text and a new text added

hope that helps

How would I write a T-SQL query to concatenate and group text

Try this

SELECT DISTINCT PERSON, STUFF((SELECT ' ' + TEXT
    FROM [YourTable] a
    WHERE a.PERSON = b.PERSON
    ORDER BY PERSON,SEQUENCE
    FOR XML PATH('')),1,1,'') as TEXT
FROM [YourTable] b

---

Related Topics