SQL Query with criteria of 6 chars and wildcards

I’m stuck with an SQL query to get what I need. My input is this:

COD SINOM
A   123456
B   987654, 123456, 111111
C   123456 , 234501
D   9912345699
E   99123456, 789012
F   77123456

Both fields are text type, even if they are numbers on it (we use text type because sometimes it has leading zeros). Mycriteria is 123456

I’m trying to query all COD where SINOM contains my criteria, but only as 6 chars in a single word. It does not matter if it has any leading blanks or commas or semicolons (all of them are possible), but len of string must be 6, and could be more text. And criteria can be at left, right or middle of SINOM.

My actual query is this:

SELECT Table1.cod, Table1.sinom
FROM Table1
WHERE (((Table1.sinom)="123456")) OR (((Table1.sinom) Like "*123456,*")) OR (((Table1.sinom) Like "*123456 *"))

But the output I’m getting is wrong:

The right ones would be only the first 3 rows (A,B,C). Rest of rows are wrong. My expected output would be:

9912345699, 99123456, 789012 and 77123456 are wrong because it contains 123456 but not as a single word.

Looking for an SQL query to apply this and trying to avoid the use of an VBA function if possible

Other SYNOM that would be correct would be:

998877, 123456, 029384
012310,123456

I hope this is clear, but please, do not hesitate to ask if any doubt arises.

Thanks!

Answer

I think you want something like this:

WHERE "," & REPLACE(Table1.sinom, " ", "") & ",") Like "*,123456,*"

The key is to look for the delimited strings — but to be sure that the column has commas at the beginning and end.

One caveat here is the spaces. This removes the spaces, assuming they are merely spaces.

Advertisement

Answer