How to Convert Oracle Sql to Postgresql

Question

My SQL query has "connect by regexp_substr". How do I convert it to PostgreSQL 10 query ?

I have tried this in Ubuntu and toad...

  select regexp_substr('1,2,4','[^,]+', 1, level) from dual
    connect by regexp_substr('1,2,4', '[^,]+', 1, level) is not null;

How to do I get convert above query to PostgreSQL version 10?

Please provide sample data, desired results, and an explanation of the logic you want to implement. — Gordon Linoff
– Gordon Linoff, Commented Jul 25, 2019 at 18:46

user330315 · Accepted Answer · 2019-07-25 20:05:12Z

2

string_to_array() is typically faster if no regular expression is needed.

select *
from unnest(string_to_array('1,2,4', ',')) as t(c);

answered Jul 25, 2019 at 20:05

user330315

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-07-25 18:48:24Z

1

If I understand correctly, you want to split a comma-delimited string into rows. For that, use regexp_split_to_table():

select regexp_split_to_table('1,2,4', ',')

In Postgres, it is possible that you would really phrase the overall query using arrays. If you have a question about a larger query, ask in a new question.

answered Jul 25, 2019 at 18:48

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to Convert Oracle Sql to Postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related