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
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:
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
I hope this is clear, but please, do not hesitate to ask if any doubt arises.
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.