I am working on converting something from Oracle to PostgreSQL. In the Oracle file there is a function:
instr(string,substring,starting point,nth location)
or as it is in my file
instr(string,chr(10),instr(string,substring),1)
In PostgreSQL this does not exist, so I looked up an equivalent function. I found:
position(substring in string)
but this does not allow the starting position and the nth location parameters.
Is there anyway to make this function start at a given point? Or is there a better function to use in PostgreSQL where I can specify starting position and the nth location?
This would have to work on PostgreSQL 8.2.15 because that is the version we are running on the database.
The function strpos(str, sub)
in Postgres is equivalent of instr(str, sub)
in Oracle. Unfortunately, the function does not have third and fourth parameters, so the expression in Postgres must be more complex.
The function substr(str, n)
gives a substring of str
starting from n
position.
instr(str, ch, instr(str, sub), 1); --oracle
strpos(substr(str, strpos(str, sub)), ch) + strpos(str, sub) - 1; --postgres
As instr()
is a powerful function I wrote it in plpgsql for my own needs.
create or replace function instr(str text, sub text, startpos int = 1, occurrence int = 1)
returns int language plpgsql immutable
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 $$;
Some checks (Examples from OLAP DML Functions):
select instr('Corporate Floor', 'or', 3, 2); -- gives 14
select instr('Corporate Floor', 'or', -3, 2); -- gives 2
There is no reverse()
function in Postgres 8.2. You can use this:
-- only for Postgres 8.4 or earlier!
create or replace function reverse(str text)
returns text language plpgsql immutable
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 $$;
The simplest form:
instr(string, substring) ::= strpos(string, substring)
With a position
parameter:
For a positive position
value:
instr(string, substring, position) ::= strpos(substr(string, position), substring) + position - 1
For a negative position
value:
instr(string, substring, position) ::= strpos(substr(string, char_length(string) + position + 1), substring) + char_length(string) + position
With an occurrence
parameter:
This does not exist in PostgreSQL. You don't seem to need it (example gives occurrence = 1
) but if you do then you need to write a function that recursively works on sub-strings extracted from the second version.
So:
instr(string,chr(10),instr(string,substring),1)
becomes
strpos(substr(string, strpos(string, substring)), chr(10)) + strpos(string, substring) - 1
You can use split_part() function in postgres
Refer to the following link
https://www.postgresqltutorial.com/postgresql-split_part/
SELECT SPLIT_PART('A,B,C', ',', 2);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With