Search code examples
sqlsqlitesubstringinstr

How to search for multiple substrings using instr()

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.

Solution

  • 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;