Regex matching the following Input in SQL Column

Question

I have a SQL Column with the following values

ReportID
--------
19
19,20,21
20,19,21
20,21,19
119,21
1191,21

I need to match the the rows which have 19 as a value (i.e. reject last two rows)

Is there a better way to do this than the query used below ? (Using combined regex)

My Query:

SELECT * 
FROM [StoreBI_Validation].[dbo].[PortalNotifications]
WHERE ReportID like '%,19,%' 
   or ReportID like '19' 
   or ReportID like '19,%' 
   or ReportID like '%,19'

The correct solution is to not store comma separated values in a column. If you properly normalize your data you wouldn't have that problem. A quick workaround is to store a leading and trailing comma, e.g. ,20,19,21, then you using %,19,% is enough — user330315
– user330315, Commented Mar 31, 2016 at 7:40
If you normalize your data, not only would you not have this problem, but your queries would work significantly faster as you could use an index on the reportID column. There's no way of using an index with your current query. — Matt Gibson
– Matt Gibson, Commented Mar 31, 2016 at 8:12

Ravi Singh · Accepted Answer · 2016-03-31 08:23:46Z

4

Try this :

SELECT * 
FROM Test
WHERE ','+ReportID+',' like '%,19,%'

SQL Fiddle

Or this :

SELECT DISTINCT reportid
FROM (
    SELECT A.ReportID
        ,Split.a.value('.', 'VARCHAR(100)') AS Data
    FROM (
        SELECT ReportID
            ,CAST('<M>' + REPLACE(ReportID, ',', '</M><M>') + '</M>' AS XML) AS Data
        FROM Test
        ) AS A
    CROSS APPLY Data.nodes('/M') AS Split(a)
    ) B
WHERE Data = '19';

edited Mar 31, 2016 at 8:23

answered Mar 31, 2016 at 7:52

Ravi Singh

2,08013 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Deepak Ankalkoti · Accepted Answer · 2016-03-31 09:40:36Z

1

SELECT * 
FROM [StoreBI_Validation].[dbo].[PortalNotifications]
WHERE CHARINDEX(',19,',ReportID)>0

answered Mar 31, 2016 at 9:40

Deepak Ankalkoti

828 bronze badges

Collectives™ on Stack Overflow

Regex matching the following Input in SQL Column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related