0

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!
Martyn Phillips
  • 25
  • 4
  • Checking lots of LIKE conditions or regex conditions can make your query very slow and should be avoided if possible. Why can't you check the whole string instead of everything but the last character? – Jonas Metzler Nov 23 '22 at 09:58
  • Thanks for replying, Im not sure I understand what you mean, sorry. If i check the whole string, they wont match? – Martyn Phillips Nov 23 '22 at 10:05
  • Ah, I see. That's the problem, the list is missing the last digit due to an error, so there is no way of knowing what the missing digit is unless I can do some kind of matching against the original reference numbers. – Martyn Phillips Nov 23 '22 at 10:14
  • that would be PayPal, so I'm looking for a quicker solution to consolidate my numbers. – Martyn Phillips Nov 23 '22 at 10:21
  • Could you please tag your DBMS? See also https://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql – Jonas Metzler Nov 23 '22 at 10:41
  • Ive tagged it, its Snowflake. Thats for the link this is interesting maybe I can make this work! – Martyn Phillips Nov 23 '22 at 11:31

1 Answers1

0

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',
...
)
NickW
  • 8,430
  • 2
  • 6
  • 19