Searching Through Table from comma delimited column using SQL

Question

I have data in the below format, I would like to find the users that match any and all of the words within the comma delimited skills column:

Name    | id   | skills                               |
-------------------------------------------------------
Bbarker | 5987 | Needles, Pins, Surgery, Word, Excel  |
CJerald | 5988 | Bartender, Shots                     |
RSarah  | 5600 | Pins, Ground, Hot, Coffee            |

So if I am searching for "Needles, Pins", it should return Bbarker and RSarahs rows.

How would I achieve something like this using SQL ?

I dont even know where to begin or what to search for, any help in the right direction would be great!

Thanks!

Don't store serialized datas. Think about [database normalization](https://en.wikipedia.org/wiki/Database_normalization). This goes against the purpose of using a RDBMS and having indexing — Cid, Oct 17 '19 at 14:59
It's a bad design to store data in comma-separated lists. I wrote this answer to describe many cases: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/a/3653574/20860). — Bill Karwin, Oct 17 '19 at 15:02
If you want a workaround, please be specific about which SQL database you use. I guess when you say "SQL" you mean Microsoft SQL Server (many users of Microsoft just say "SQL" — if they used a text editor, I bet they'd just call it "Word"). — Bill Karwin, Oct 17 '19 at 15:03
Sadly I have inherited the setup, but I am taking everyones points in mind, and it does seem evident that its not a good way of doing things, so I will work on completely changing the way that works. Thanks to everyone for bringing this to light and pointing me in the right direction. — AxleWack, Oct 17 '19 at 15:08
I'm voting to close this question as it has come to light that the solution I am looking for goes against best practices, and will serve no purpose helping any one else. — AxleWack, Oct 17 '19 at 15:14
Begin by learning proper database design, and change your table structure. — Eric, Oct 17 '19 at 18:30
@Eric - As I mentioned, I inherited the setup, so I was trying to make it work, I am currently busy redesigning the way this was done, so thanks for your insight-fully helpful comment. — AxleWack, Oct 18 '19 at 04:52

score 2 · Answer 1 · answered Oct 17 '19 at 18:03

Poor design aside, sometimes we are stuck and have to deal with that poor design.

I agree that if you have the option of redesigning I would pursue that route, in the meantime there are ways you can deal with delimited data.

If you are SQL Server version 2016+ there is a built in function call STRING_SLIT() that can be used. If you are prior to SQL Server 2016 you basically have to convert to XML as a workaround

Here's a working example of both you can explore:

DECLARE @TestData TABLE
    (
        [Name] NVARCHAR(100)
      , [Id] INT
      , [skills] NVARCHAR(100)
    );
--Test data
INSERT INTO @TestData (
                          [Name]
                        , [Id]
                        , [skills]
                      )
VALUES ( 'Bbarker', 5987, 'Needles, Pins, Surgery, Word, Excel' )
     , ( 'CJerald', 5988, 'Bartender, Shots' )
     , ( 'RSarah', 5600, 'Pins, Ground, Hot, Coffee' );

--search words
DECLARE @Search NVARCHAR(100) = 'Needles, Pins';

--sql server 2016+ using STING_SPLIT
SELECT      DISTINCT [a].*
FROM        @TestData [a]
CROSS APPLY STRING_SPLIT([a].[skills], ',') [sk]  --split your column
CROSS APPLY STRING_SPLIT(@Search, ',') [srch] --split your search
WHERE       LTRIM(RTRIM([sk].[value])) = LTRIM(RTRIM([srch].[value]));  --filter where they equal

--Prior to sql server 2016, convert XML
SELECT      DISTINCT [td].*
FROM        @TestData [td]
--below we are converting to xml and then spliting those out for your column
CROSS APPLY (
                SELECT      [Split].[a].[value]('.', 'NVARCHAR(MAX)') [value]
                FROM        (
                                SELECT CAST('<X>' + REPLACE([td].[skills], ',', '</X><X>') + '</X>' AS XML) AS [String]
                            ) AS [A]
                CROSS APPLY [String].[nodes]('/X') AS [Split]([a])
            ) AS [sk]
--same here for the search
CROSS APPLY (
                SELECT      [Split].[a].[value]('.', 'NVARCHAR(MAX)') [value]
                FROM        (
                                SELECT CAST('<X>' + REPLACE(@Search, ',', '</X><X>') + '</X>' AS XML) AS [String]
                            ) AS [A]
                CROSS APPLY [String].[nodes]('/X') AS [Split]([a])
            ) AS [srch]
WHERE       LTRIM(RTRIM([sk].[value])) = LTRIM(RTRIM([srch].[value])); --then as before where those are equal

Both will get you the output of:

Name        Id       skills
----------  -------  ------------------------------------
Bbarker     5987     Needles, Pins, Surgery, Word, Excel
RSarah      5600     Pins, Ground, Hot, Coffee

Thanks for the comment Tim, but I think its best that I redesign and redevelop the way it was done. Your answer holds what all others should learn from, advise for the best solution, but then also a solution to the current problem. I think its best to fix this problem, rather than create a hack that will cause more complications later. Thanks again! — AxleWack, Oct 18 '19 at 04:59

score 0 · Answer 2 · answered Oct 17 '19 at 15:02

0

How about this?

SELECT DISTINCT Name, id
  FROM table
 WHERE skills LIKE '%Needles%'
    OR skills LIKE '%Pins%'

answered Oct 17 '19 at 15:02

OTTA

1,071
7
8

2

What if one of the words in the list is "Needless" or "Pinstripe"? – Bill Karwin Oct 17 '19 at 15:03
2

LOL - so you're going to include a NOT term for every imaginable word that you _don't_ want to match? Also, how would you handle the case where a list contains _both_ "Pins" and "Pinstripe" so you do want to match the list, even though it contains "Pinstripe"? – Bill Karwin Oct 17 '19 at 15:05

Searching Through Table from comma delimited column using SQL

2 Answers2