Замена Oracle INSTR в SQLite

В настоящее время я портирую часть приложения из Oracle на сервер SQLite (Java, используя простой JDBC). Часто используемая особенность Oracle - это функция INSTR с тремя аргументами:

INSTR (<строка>, <строка-поиска>, <позиция>)

Эта функция ищет в строке строку поиска, начиная с определенной позиции. Третий параметр может быть как положительным, так и отрицательным. Если оно отрицательное, поиск работает в обратном направлении, начиная с конца строки.

Эта функция недоступна в SQLite, и лучшее, что я мог придумать, - это альтернатива вложением некоторых других функций:

Если <позиция> положительна:

ДЛИНА (<строка>) - ДЛИНА (SUBSTR (SUBSTR (<строка>, <позиция>), STRPOS (SUBSTR (<строка>, <позиция>), <строка-поиска>) + 1))

Если <position> отрицателен (в нашем случае -1 - единственное используемое отрицательное значение):

ДЛИНА (<строка>) - ДЛИНА (ЗАМЕНА (<строка>, RTRIM (<строка>, ЗАМЕНА (<строка>, <строка-поиска>, '')), ''))

Кажется, это дает желаемый результат, но вы можете понять, почему я не очень поддерживаю такой подход. Конечно, потому что в оригинальном синтаксисе INSTR используется часто и также является вложенным. Это становится катастрофой для технического обслуживания впоследствии.

Есть ли более элегантный подход, или я мог упустить какое-то другое нативное решение для того, что кажется довольно тривиальной задачей?

Ответ 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

Предполагается, что символ ¬ не будет частью строки поиска (но в маловероятном случае это предположение ложно, конечно, может быть изменено на другой редко используемый символ).

SQLFiddle Demo

Некоторые сработанные примеры здесь: http://sqlfiddle.com/#!5/7e40f9/5

кредиты

Метод положительной position был адаптирован из ответа Тима Бигелейзена. (Но нулевое значение нужно обрабатывать отдельно).
Метод отрицательной position использовал метод, описанный в этом вопросе в качестве отправной точки.
Создание строки, состоящей из символа, повторенного n раз, было взято из этого упрощенного ответа.

Ответ 2

На самом деле, SQLite поддерживает функцию INSTR. Но у него нет третьего параметра, что означает, что он всегда ищет с самого начала строки.

Но мы можем обойти это, передав подстроку в INSTR, а затем INSTR позицию, добавив величину смещения подстроки.

Итак, в качестве примера, вызов Oracle:

INSTR('catsondogsonhats', 'on', 7)

который вернул бы 11, стал бы:

INSTR(SUBSTR('catsondogsonhats', 7), 'on') + 6