How do you use a list to find a partial match in a table column with SQL?-CodePudding

I have a large list of reference numbers, for example:

'abc 123',
'abc 456',
'abc 789',
...

The problem is that my list is missing the last digit, so when I search my datatable to see if these references from my list are in my datatable, I find not matches.

In SQL can you use a WHERE IN clause with some sort of reg exp LIKE statement, to capture the closest match to each reference number?

I imagine there is a very manual work around, but I have a pretty long list so writing a rule for each reference wouldn't be efficient.

I would normally do:

SELECT *
FROM table
WHERE "reference_number" IN
('abc 123',
'abc 456',
'abc 789',
...
)

In my datatable, the correct references would have an additional number on the end:

'abc 1235',
'abc 4567',
'abc 7891',
...

Any help would be very welcome, thank you!

CodePudding user response：

Just compare your list to the column with the column minus the last character e.g.

SELECT *
FROM table
WHERE left("reference_number", len("reference_number")-1) 
IN
('abc 123',
'abc 456',
'abc 789',
...
)