Oracle INSTR replacement in SQLite
SQL
CASE WHEN position = 0 THEN INSTR(string, substring) WHEN position > 0 THEN INSTR(SUBSTR(string, position), substring) + position - 1 WHEN position < 0 THEN LENGTH(RTRIM(REPLACE(string, substring, REPLACE(HEX(ZEROBLOB(LENGTH(substring))), '00', '¬')), string)) - LENGTH(substring) + 1 END
It assumes the ¬
character won't be part of the search string (but in the unlikely event this assumption is false could of course be changed to a different rarely used character).
SQLFiddle Demo
Some worked examples here: http://sqlfiddle.com/#!5/7e40f9/5
Credits
- The positive
position
method was adapted from Tim Biegeleisen's answer. (But a zero value needs to be handled separately). - The negative
position
method used the method described in this question as a starting point. - The creation of a string consisting of a character repeated n times was taken from this simplified answer.
Actually, SQLite does support an INSTR
function. But, it does not have a third parameter, which means, it always searches from the very beginning of the string.
But, we can workaround this by passing a substring to INSTR
, and then offsetting the position found by adding the amount of the offset of the substring.
So, as an example, Oracle's call:
INSTR('catsondogsonhats', 'on', 7)
which would return 11
, would become:
INSTR(SUBSTR('catsondogsonhats', 7), 'on') + 6