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:

• T1
• T1, T3, T11, T12
• T26, T1, T11
• T18, T1
• T2, T3

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:

• %T1% finds also T11, T12, etc...
• %T1 works only if T1 is the last item
• T1 works only if T1 is the only item
• T1% works only if T1 is the first item

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?

CodePudding user response：

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

CodePudding user response：

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,%'
    )

CodePudding user response：

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

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