Comma Separated Values in one table to return results from another table

Question

I have two tables and a fairly complicated SQL query to extract data from these tables - this all works fine until it comes across a value within a column which has more than one id - comma separated. So to simplify the area I'm struggling with, let's assume the following

Table 1 (T1)

ID         First Name     Last Name   Active  
--------------------------------------------
101        Fred           Bloggs      1  
102        John           Smith       0  
103        Elizabeth      Dawson      1  
104        Amy            Johnson     1

Table 2 (T2)

ID         Postcode       HouseNo  
-----------------------------------
101        TS15 9AZ         42   
102        TQ1 4TF           3  
103, 104   WA1 4AA           7

So assuming I want to return results of who lives at what address, I join the tables on ID and write a fairly simple query like

select 
    T1.FirstName + ' ' + T1.Lastname as fullname, T2.Postcode, T2.HouseNo
from 
    T1
join 
    t2 on t1.id = t2.id
where 
    t1.active = 1

This query works fine until it comes across the comma separated value when it returns the error:

Conversion failed when converting the varchar value '103,104' to data type int

What it should return is

Fullname                         PostCode      HouseNo
-------------------------------------------------------
Fred Blogs                       TS15 9AZ        42
Elizabeth Dawson Amy Johnson     TQ1 4TF          3

Any ideas on how to make this work?

Storing **comma-separated** lists of values in a single database cell is a **big NO-NO** and will - as you see here - only cause you grief and heartache. You should obey the **first normal form** of database design - a single cell contains **at most ONE atomic value** - handle multiple values in a proper, relational fashion — marc_s, Aug 17 '20 at 16:57
I think you will see comments here on table 2's design. The error you are receiving is due to T1.ID being an INT and T2.ID a string (with an example with ','). Joining these two columns will fail. Change T2 to have two separate rows for ID 103 and 104 - then the JOIN will work. — AhmedHuq, Aug 17 '20 at 16:59

score 1 · Accepted Answer · answered Aug 17 '20 at 17:08

As a starter: don't store multiple values in a single column; don't use strings to store numbers. You can have a look at this famous SO post for more details on why this is discouraged.

That said, a simple (although inefficient) solution to search for a value in a CSV list is:

select t1.FirstName + ' ' + t1.Lastname as fullname, t2.Postcode, t2.HouseNo
from t1
join t2 on concat(', ', t2.id, ', ') like concat('%, ', t1.id, ', %')
where t1.active = 1

This assumes that you consistently use a comma + a space (', ') as separator between list elements.

Thanks for everyone's respone - very much appreciated. I used this solution because although it excluded the results with multiple ID's it was then an easy job to rerun for those using a different field. — Will_UK, Aug 18 '20 at 17:51

Arsen Khachaturyan · Answer 2 · 2020-08-17T17:13:11.773

Your query is failing because the data your table is returning seems not consistent in the return type, because it has both integer values (103, 104) and non-integer ones, like the ones you mentioned in your desired output.

The solution here is to convert them all to a single type. I see the string result type as probably the best option here:

select
    T1.FirstName + ' ' + T1.Lastname as fullname, 
    CONVERT(NVARCHAR(10), T2.Postcode), -- you can change the value 10 to anything else
    CONVERT(NVARCHAR(10), T2.HouseNo)
from 
    T1
join 
    t2 on t1.id = t2.id
where 
    t1.active = 1

Hope this will help ))

Update

@marc_s is absolutely correct here, try to avoid using comma-separated values in your table columns. They are violating the SQL normalization rules (official documentation here).

TMcMaster · Answer 3 · 2020-08-17T17:22:15.453

Sometimes it might be data we have inherited and you just need a fix for reporting. I found this splitstring function on the internet a while ago (kudos to whomever wrote it) and it will split your comma separated ID values up (if you are using a database version earlier than SQL Server 2016 where STRING_SPLIT isn't available), which you could perhaps put into another table and work with that? I agree with the other comments though, it would 100% be a good move to keep a single value in a field.

CREATE or alter FUNCTION [dbo].[SplitString]  
(  
   @Input NVARCHAR(MAX),  
   @Character CHAR(1)  
)  
RETURNS @Output TABLE (  
   Item NVARCHAR(1000)  
)  
AS  
BEGIN  
    DECLARE @StartIndex INT, @EndIndex INT  
    SET @StartIndex = 1  

    IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character  
    BEGIN  
        SET @Input = @Input + @Character  
    END  

    WHILE CHARINDEX(@Character, @Input) > 0  
    BEGIN  
        SET @EndIndex = CHARINDEX(@Character, @Input)  

        INSERT INTO @Output(Item)  
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)  
            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))  
    END  
RETURN  
END 

GO

--=======================================================
DROP TABLE IF EXISTS newTable;

SELECT * INTO newTable 
FROM
(
    SELECT '123,456' as id, 'TS15 9AZ' AS postcode UNION
    SELECT '456,789' as id, 'TQ1 4TF' AS postcode
) AS IDS
CROSS APPLY
DBO.[SPLITSTRING](ID, ',') AS SPLIT;

SELECT * FROM newTable;
--=======================================================

score 0 · Answer 4 · answered Aug 17 '20 at 17:14

0

As advised multiple times above, it would be best to store the values in the ID column separately. That said in SQL Server you could do it this way:

select 
    T1.FirstName + ' ' + T1.Lastname as fullname, T2.Postcode, T2.HouseNo
from 
    T1
join 
(
    select t2.*, value as id_new
    from t2
    CROSS APPLY STRING_SPLIT(id, ',')
) t2 on t1.id = t2.id_new
where 
    t1.active = 1

answered Aug 17 '20 at 17:14

jmm312

618
1
6
17

That would have worked. But the question is tagged SQL Server 2012: `string_split()` is available starting version 2016 only. – GMB Aug 17 '20 at 17:25

score 0 · Answer 5 · answered Aug 17 '20 at 17:16

0

You can try the query below

SELECT T1.FirstName + ' ' + T1.LastName AS FullName, T2.PostCode, T2.HouseNo
FROM T1
JOIN (
    SELECT LTRIM(RTRIM(ID)), PostCode, HouseNo
    FROM T2
    CROSS APPLY STRING_SPLIT(ID, ',')
) T2 ON T2.ID = T1.ID
WHERE T1.active = 1

answered Aug 17 '20 at 17:16

Eric

3,165
1
19
25

The question is tagged SQL Server 2012: `string_split()` is available starting version 2016 only. – GMB Aug 17 '20 at 17:25

Comma Separated Values in one table to return results from another table

5 Answers5