Why SQL Server Ignores Vaules in String Concatenation When Order by Clause Specified

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

Mysql: Concatenate Duplicate Data but ignore string in duplicates

To do what you want you probably need CTEs (Common Table Expressions), and LATERAL queries. Unfortunately MySQL 5.x does not implement either of them.

The following query finds the duplicate names:

select plain_name, count(*)
  from (
    select name, trim(replace(lower(name), lower('Dr.'), '')) as plain_name
      from my_table
  ) x
  group by plain_name
  having count(*) > 1

This is a step in the right direction, but you'll need to process further to get the result you want.

If you upgrade to MySQL 8 you will get CTEs, but still won't get LATERAL queries.

Edit: I went a step further to identify the duplicate names. Without CTEs this query is looking increasingly uglier:

select z.*, y.times
  from (
    select name, trim(replace(lower(name), lower('Dr.'), '')) as plain_name
      from my_table
  ) z,
  (
    select plain_name, count(*) as times
      from (
        select name, trim(replace(lower(name), lower('Dr.'), '')) as plain_name
          from my_table
      ) x
      group by plain_name
      having count(*) > 1
  ) y
  where z.plain_name = y.plain_name;

Concatenate with NULL values in SQL

Use the COALESCE function to replace NULL values with an empty string.

SELECT Column1 + COALESCE(Column2, '') AS Result
    FROM YourTable

Using IF condition inside CONCAT function of SQL Query

Concat will ignore NULL values when appending. Try this

select CONCAT(technology, ',' +secondary_technology, ',' +tertiary_technology)
from deals

If technology column could be null.

select case when technology is null then stuff(Result, 1, 1, '') else Result end
from (
select technology, CONCAT(technology, ',' +secondary_technology, ',' +tertiary_technology) as Result
from deals
) tab

You might also want to check for Empty string columns using NULLIF.

select case 
    when coalesce(technology,'') = '' then stuff(Result, 1, 1, '') 
    else Result 
  end Result
from 
(
  select technology
  , CONCAT(technology, ',' + nullif(secondary_technology,''), ',' + nullif(tertiary_technology,'')) as Result
  from deals
) tab

Can't concatenate to strings with CHAR(0) at the end

SQL server concatenates strings ending in nul characters just fine. See this example:

SELECT len('"Hello World' + CHAR(0) + '"')

Output:

14

SELECT len('"Hello World' + CHAR(0) + '"' + '"Hello World' + CHAR(0) + '"')

Output:

28

The result is the same when you store the string into a CTE or table first.

Its the handling and output in VB that makes it appear as if it does not. Try to print the length of the string your are getting out of SQL in VB.

SQL using If Not Null on a Concatenation

Here would be my suggestions:

PostgreSQL and other SQL databases where 'a' || NULL IS NULL, then use COALESCE:

SELECT firstname || COALESCE('-' || middlename, '') || '-' || surname ...

Oracle and other SQL databases where 'a' || NULL = 'a':

SELECT first name || DECODE(middlename, NULL, '', '-' || middlename) || '-' || surname...

I like to go for conciseness. Here it is not very interesting to any maintenance programmer whether the middle name is empty or not. CASE switches are perfectly fine, but they are bulky. I'd like to avoid repeating the same column name ("middle name") where possible.

As @Prdp noted, the answer is RDBMS-specific. What is specific is whether the server treats a zero-length string as being equivalent to NULL, which determines whether concatenating a NULL yields a NULL or not.

Generally COALESCE is most concise for PostgreSQL-style empty string handling, and DECODE (*VALUE*, NULL, ''... for Oracle-style empty string handling.

Will multiple columns concatenate in the same order if using STUFF and For Xml Path

You can add an ORDER BY clause in the query within your STUFF function

stuff() adds separator even when fields are empty

add a condition to the WHERE clause to exclude rows with all value empty string

select stuff((
           select '; ' + ([FIELD_1] + [FIELD_2] + [...] + [FIELD_N])
           from   [TABLE] t1
           where  t1.[ID] = t2.[ID]
           and    [FIELD_1] + [FIELD_2] + [...] + [FIELD_N] <> ''
           for    xml path ('')
        ),1,1, '')
from    [TABLE] t2

T-SQL: Best way to handle NULL values in string concatenation

To predictably look correct with commas between every two fields, you can use this form

;with users(City, State, Country) as (
select 'a', null, 'c' union all
select 'a', 'b', 'c' union all
select null, null, 'c')

-- ignore above this line
SELECT City, State, Country,
    STUFF(
        ISNULL(', ' + City, '')+
        ISNULL(', ' + State, '')+
        ISNULL(', ' + Country, ''), 1, 2, '') AS 'Location'
FROM Users

Output

City State Country Location
---- ----- ------- --------
a    NULL  c       a, c
a    b     c       a, b, c
NULL NULL  c       c

Preserving ORDER BY in SELECT INTO

What for?

Point is – data in a table is not ordered. In SQL Server the intrinsic storage order of a table is that of the (if defined) clustered index.

The order in which data is inserted is basically "irrelevant". It is forgotten the moment the data is written into the table.

As such, nothing is gained, even if you get this stuff. If you need an order when dealing with data, you HAVE To put an order by clause on the select that gets it. Anything else is random - i.e. the order you et data is not determined and may change.

So it makes no sense to have a specific order on the insert as you try to achieve.

SQL 101: sets have no order.

---

Related Topics