Search code examples
sqldatabasems-access-2016

MSAccess - SQL query everything to the right OR left of a character

Using Ms Access 2016 I'm trying to run a SELECT query to match strings on a column from table1 and table2. I can do this with the following:

SELECT *
FROM table1 AS a, table2 AS b
WHERE a.luCode LIKE b.Code

table1.luCode only eve rhas one code, however, table2.Code sometimes has two codes in it separated by ";":

table2.Code
--------------------
someCode1
someCode2
someCode3;someCode4
someCode5

How can I perform the above query checking both to the left and right of the ";"?

So far I've been trying to use InStr (just to the left) using:

SELECT *
FROM table1 AS a, table2 AS b
WHERE a.luCode LIKE LEFT(b.Code,(InStr(1,b.Code,";"))-1);

But I get a datatype mismatch. Figuring this would be either "null" values of empty strings (after checking it seems it's empty strings) I can add an IIF statement:

SELECT *
FROM table1 AS a, table2 AS b
WHERE IIF(b.Code<>"",a.luCode LIKE LEFT(b.Code,(InStr(1,b.Code,";"))-1));

This throws an "invalid procedure call" error.

I could just make a new column with the data after the ";" but there must be a way to do this with InStr.

Solution

  • First, this is a really, really bad data format. You should have a table with one row for each code, instead of throwing them together in a string.

    Sometimes, we are stuck with other people's bad design decisions. In this case, you can try:

    WHERE ';' & b.luCode & ';' LIKE '*;' & a.Code & ';*'

    Or alternatively use instr() with the same logic.