INSTR - функция. Перевод с Oracle на Postgres

Question

Есть такой запрос на Oracle (SQL Developer):

SELECT MAX (COMM.CREATION_DATE)  mcd,
SUBSTR (SOURCEUUID, INSTR (SOURCEUUID, '$') + 1)     cid
FROM TBL_COMMENT comm

При переводе на Postgres, не нашел эквивалентной функции INSTR. Есть ли её аналоги или как составить аналогичный запрос на Postgres?

Сам запрос выдает: дату формата ДД.ММ.ГГ время и какое-то число.

Answer 1

POSITION(search_string in main_string), доступен с 1997 года ~~PostgreSQL 9.3~~

SELECT 
  MAX (COMM.CREATION_DATE)  mcd,
  SUBSTR (SOURCEUUID, POSITION('$' in SOURCEUUID) + 1) cid
FROM TBL_COMMENT comm
GROUP BY SUBSTR (SOURCEUUID, POSITION('$' in SOURCEUUID) + 1)

Answer 2

bash

res="`psql -A -t -c "SELECT split_part(fio, '<br>', 1) FROM driver WHERE idx=$i;"`"

так же хотел использовать instr для удаления лишнего, получилось так: удаляем все что начинатся с <br> и остается соответсвенно все слева от этого, выдерживайте кавычки иначе не полностью подтянет данные

Answer 3

Функция strpos(str, sub) в Postgres SQL эквивалентна instr(str, sub) в Oracle.

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

Функция substr(str, n) дает подстроку str, начиная с позиции n.

instr(str, ch, instr(str, sub), 1);                               --Oracle
strpos(substr(str, strpos(str, sub)), ch) + strpos(str, sub) - 1; --Postgres

Так как instr() является очень полезной функцией, то переписал ее в PL/pgSQL:

create or replace function instr(str text, sub text, startpos int = 1, occurrence int = 1)
returns int language plpgsql
as $$
declare 
    tail text;
    shift int;
    pos int;
    i int;
begin
    shift:= 0;
    if startpos = 0 or occurrence <= 0 then
        return 0;
    end if;
    if startpos < 0 then
        str:= reverse(str);
        sub:= reverse(sub);
        pos:= -startpos;
    else
        pos:= startpos;
    end if;
    for i in 1..occurrence loop
        shift:= shift+ pos;
        tail:= substr(str, shift);
        pos:= strpos(tail, sub);
        if pos = 0 then
            return 0;
        end if;
    end loop;
    if startpos > 0 then
        return pos+ shift- 1;
    else
        return length(str)- length(sub)- pos- shift+ 3;
    end if;
end $$;

Некоторые тесты (примеры из гл. OLAP DML Functions:

select instr('Corporate Floor', 'or', 3, 2);  -- 14
select instr('Corporate Floor', 'or', -3, 2); --  2

В Postgres 8.2 нет функции reverse(), поэтому можно использовать это:

-- только для Postgres 8.4 или раньше!
create or replace function reverse(str text)
returns text language plpgsql
as $$
declare
    i int;
    res text = '';
begin
    for i in 1..length(str) loop
        res:= substr(str, i, 1) || res;
    end loop;
    return res;
end $$;

_{Свободный перевод ответа от участника @klin}

БЛОГ НА HUSL

INSTR - функция. Перевод с Oracle на Postgres

Ответы (3 шт):