Equivalent of regexp_instr (ORACLE) in Postgresql-CodePudding

I came across an Oracle function when converting Oracle schema to Postgresql 12 as following:

regexp_instr(inColumnDdl, reNumberXY, 1, 1, 0, 'i', 0) > 0 then

can you tell me please the equivalent of that function in Postgresql known that the regular expression "regexp_instr" is not supported by postgresql 12

CodePudding user response：

Can you try this?

SELECT strpos(inColumnDdl, (regexp_match(inColumnDdl, reNumberXY, 'i'))[1]);

CodePudding user response：

As your condition only checks if the the position is greater than zero, you seem to be only interested if the regex pattern is found, not where it is found.

In that case you can simply use the regex operator ~* that does a case insensitive match.

if inColumnDdl ~* reNumberXY then

If you want a case sensitive match, use ~