How to search for rows that are a substring of a value in MySQL?

Question

I have a full length item code that I am looking for, '34FDE353UE2' and a table full of a shortened version of said item codes, like this

itemCode
============
34FDE
35DCF
34FPE
....

How can I look through my rows to see if any of them match the '34FDE353UE2'? In my example, I would hope to get back the first row since 34FDE is a substring of '34FDE353UE2'. How can I write a query to do this?

Using MySQL 5.6.

if i could downvote comments i would @SuperMario w3schools had a history with incorrect/incomplete documentation and or outdated and unsafe programming tutorials in the past — Raymond Nijland, Mar 07 '19 at 15:04

score 5 · Answer 1 · answered Mar 07 '19 at 14:47

5

With like:

select * from tablename
where '34FDE353UE2' like concat(itemCode, '%')

this will return rows where itemCode's value is the starting chars of '34FDE353UE2'.
If you want rows where itemcode is a substring of '34FDE353UE2' then:

select * from tablename
where '34FDE353UE2' like concat('%', itemCode, '%')

answered Mar 07 '19 at 14:47

forpas

160,666
10
38
76

That was the easiest way. Thank you. I'd give you the solution but turns out my question was a duplicate. – bjk116 Mar 07 '19 at 14:58

score 0 · Answer 2 · answered Mar 07 '19 at 14:55

0

this will work:

select * from tablename where  REGEXP_SUBSTR(itemCode,'34FDE353UE2')

answered Mar 07 '19 at 14:55

Nikhil S

3,786
4
18
32

Ideally you should not use the regex engine for something which can be done without with simple string matching functions. – Raymond Nijland Mar 07 '19 at 15:02
1

@RaymondNijland i am not ideal i am a human being!!! :-) – Nikhil S Mar 07 '19 at 15:07
1

@RaymondNijland i know the simple ones!!! :-) – Nikhil S Mar 07 '19 at 15:08
indeed very true that also means your smarter then a machine the optimizer does not know or does not care to replace `REGEXP_SUBSTR(itemCode,'34FDE353UE2')` with a simple string matching function,, – Raymond Nijland Mar 07 '19 at 15:09
Can you **explaim** why that will work? Keep in mind that others should be able to learn from your answer – Nico Haase Mar 07 '19 at 15:23

How to search for rows that are a substring of a value in MySQL?

2 Answers2