Oracle INSTR replacement in SQLite

Question

I'm currently porting part of an application from an Oracle to a SQLite backend (Java, using plain JDBC). One Oracle-specific feature often being used is the INSTR function with three arguments:

INSTR(<string>, <search-string>, <position>)

This function searches within a string for a search string starting from a certain position. The third parameter can either be positive or negative. If it's negative, the search works backwards starting at the end of the string.

This function isn't available in SQLite and the best I could come up with is an alternative by nesting some other functions:

If <position> is positive:

LENGTH(<string>) - LENGTH(SUBSTR(SUBSTR(<string>, <position>), STRPOS(SUBSTR(<string>, <position>), <search-string>) + 1))

If <position> is negative (in our case -1 is the only negative value being used):

LENGTH(<string>) - LENGTH(REPLACE(<string>, RTRIM(<string>, REPLACE(<string>, <search-string>, '')), ''))

This seems to be giving the desired result, but you can see why I'm not really in favor of this approach. Certainly because in the original syntax the INSTR is used a lot and is being nested as well. It becomes a disaster for maintenance afterwards.

Is there a more elegant approach or could I be missing some other native solution for what seems to be a rather trivial task?

Answer 1

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

1. The positive position method was adapted from Tim Biegeleisen's answer. (But a zero value needs to be handled separately).
2. The negative position method used the method described in this question as a starting point.
3. The creation of a string consisting of a character repeated n times was taken from this simplified answer.

Answer 2

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