How to search for multiple substrings using instr()

Question

I'm trying to get the position of a file extension in a given string using instr(). This could be a number of possible extensions, so I need search for multiple substrings (eg. "jpg", "mp4"...).

I've tried an OR statement, eg. instr(string,"jpg" OR "mp4"), with no luck.

SELECT instr(column, "jpg") FROM table;

Any ideas for solutions or alternatives would be welcome.

I removed the inconsistent database tags. Please tag only with the database you are really using. Sample data and desired results would really help. — Gordon Linoff, Jul 31 '19 at 11:52

score 2 · Accepted Answer · answered Jul 31 '19 at 03:05

2

This one is what you need.

SELECT CASE WHEN instr(column, "jpg") > 0 
   THEN instr(column, "jpg") WHEN instr(column, "mp4") > 0 
   THEN instr(column, "mp4") ELSE 0 END  FROM table;

answered Jul 31 '19 at 03:05

Ed Bangga

12,879
4
16
30

forpas · Answer 2 · 2019-08-01T22:48:53.400

Assuming that only one of 'jpg', 'mp4' or any other extension may exist in the column, then you can use this:

SELECT instr(column, "jpg") + instr(column, "mp4") + .... FROM table;

For all the non existing extensions instr() will return 0 so the result will be the starting position of the existing extension.
If you store all the extensions in a table with name extensions with a single column name, you could cross join it to your table and get the results:

select
  t.column, sum(instr(t.column, e.name)) position
from table t cross join extensions e
group by t.column

How to search for multiple substrings using instr()

2 Answers2