CODEWITHSUNDEEP
sqlsql-servercsv
Mark
Mark

Reputation: 5038

SQL server: using LIKE keyword to find exact match in a CSV field

I read the docs and some questions like this and this. Still I have some issues to get my query to work.

In my table I have a varchar field that contains values separated by comma. Examples of real values are:

Note: the spaces can be present or not.

My query must find all the records that have, say, T1 in this field but not T11. IN the case above all the first 4 rows should be returned.

I know that the pattern for the LIKE operator is defined using the placeholder %, _, [] and [^].

But:

So I ended up with this query:

field LIKE '%T1' OR field LIKE 'T1,%' OR field LIKE '%,T1,%`

The first clause finds T1 if it's the only item or the last item. The second clause finds T1 if it's the first item. The last clause finds T1 if it's in the middle of other items.

But it does not work if there are spaces (i.e. T1 , T2). I should add all the other cases... It seems a bit ugly to me. Is there a more elegant way to achieve the same goal?

Upvotes: 0

Views: 539

Answers (4)

Sayroyten
Sayroyten

Reputation: 60

This should work without replace

select * from 
(values ('T1'),
('T1, T3, T11, T12'),
('T18, T1'),
('T18, T11'),
('T2, T3'))
as X(field )
where field like '%T1[^0-9]%'
 or field like '%T1'

Upvotes: 0

GarethD
GarethD

Reputation: 69769

If you replace all the spaces, then add a leading and a trailing delimiter to your column, e.g.

 CONCAT(',', REPLACE(field, ' ', ''), ',')

converts T1, T3, T11, T12 into ,T1,T3,T11,T12,

You can then search for just ,T1, since you've taken care of the start and end cases by adding the extra delimiters, e.g.

 WHERE CONCAT(',', REPLACE(field, ' ', ''), ',') LIKE '%,T1,%';

Or, if you are using a version of SQL Server that supports it, you could use:

WHERE  EXISTS
       (   SELECT  1
           FROM    STRING_SPLIT(Field, ',') AS ss
           WHERE   TRIM(ss.value) = 'T1'
       );

I wouldn't expect this to outperform LIKE though

Examples on db<>fiddle

Upvotes: 2

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112382

You can remove the spaces and add a comma at the beginning and at the end.

WHERE
    (',' + REPLACE(field, ' ', '') + ',') LIKE '%,T1,%`

Upvotes: 1

JNevill
JNevill

Reputation: 50044

You can replace out space characters to help clean this up:

WHERE 
    (
       REPLACE(field, ' ', '') LIKE '%,T1' 
       OR REPLACE(field, ' ', '') LIKE 'T1,%'
       OR REPLACE(field, ' ', '') LIKE '%,T1,%'
    )

Upvotes: 0

Related Questions