13

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
TheWhiteRabbit
  • 1,253
  • 1
  • 5
  • 18

2 Answers2

6

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.
Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
  • Not really sure if the negative position suggestion is an improvement over my current approach, still uses a lot of nesting and also not that elegant to maintain. But after reading this and doing some additional research, it seems there isn't really a way around this and will be something we have to live with. – TheWhiteRabbit Jun 03 '19 at 06:46
  • 1
    For me, the method described in the question didn't work for negative values unless the string being found was a character not found in the rest of the string - see the `OP_instr` values towards the end in [this fiddle](http://sqlfiddle.com/#!5/7e40f9/20). – Steve Chambers Jun 03 '19 at 13:57
5

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
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks, I knew about the INSTR with 2 arguments, but didn't thought about using it in this construct. Your proposal seems to work fine with positive position parameters and is already a lot less complex compared to my approach. I'm afraid negative positions are less trivial though. – TheWhiteRabbit May 27 '19 at 12:06