Oracle to PostgreSQL query conversion with string_to_array()
NickName:Dhaval Patel Ask DateTime:2015-02-13T15:14:52

Oracle to PostgreSQL query conversion with string_to_array()

I have below query in Oracle:

SELECT to_number(a.v_VALUE), b.v_VALUE
       FROM TABLE(inv_fn_splitondelimiter('12;5;25;10',';')) a
       JOIN TABLE(inv_fn_splitondelimiter('10;20;;', ';')) b
         ON a.v_idx = b.v_idx

which give me result like:

enter image description here

I want to convert the query to Postgres. I have tried a query like:

SELECT UNNEST(String_To_Array('10;20;',';'))

I have also tried:

SELECT a,b
       FROM (select  UNNEST(String_To_Array('12;5;25;10;2',';'))) a
       LEFT JOIN (select  UNNEST(String_To_Array('12;5;25;10',';'))) b
         ON a = b

But didn't get a correct result.
I don't know how to write query that's fully equivalent to the Oracle version. Anyone?

Copyright Notice:Content Author:「Dhaval Patel」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/28494357/oracle-to-postgresql-query-conversion-with-string-to-array

Answers
Erwin Brandstetter 2015-02-13T07:57:17

Starting with Postgres 9.4 you can use unnest() with multiple arrays to unnest them in parallel:\n\nSELECT *\nFROM unnest('{12,5,25,10,2}'::int[]\n , '{10,20}' ::int[]) AS t(col1, col2);\n\n\nThat's all. NULL values are filled in automatically for missing elements to the right.\n\nIf parameters are provided as strings, convert with string_to_array() first. Like:\n\nSELECT *\nFROM unnest(string_to_array('12;5;25;10', ';')\n , string_to_array('10;20' , ';')) AS t(col1, col2);\n\n\nMore details and an alternative solution for older versions:\n\n\nUnnest multiple arrays in parallel\nSplit given string and prepare case statement\n",


More about “Oracle to PostgreSQL query conversion with string_to_array()” related questions

Oracle to PostgreSQL query conversion with string_to_array()

I have below query in Oracle: SELECT to_number(a.v_VALUE), b.v_VALUE FROM TABLE(inv_fn_splitondelimiter('12;5;25;10',';')) a JOIN TABLE(inv_fn_splitondelimiter('10;20;;', ';')) b

Show Detail

Unnest(String_to_array) conversion in oracle

I am migrating procedural structure of PostgreSQL code to Oracle. Is there any alternative function present in Oracle for PostgreSQL's unnest(string_to_array)? select a.finalval from (select unn...

Show Detail

Oracle to Postgresql Query Conversion

Actually I have some oracle query which is about oracle database default tables. That is working in the oracle. Now I want to execute those queries in my postgresql database. What is the correct w...

Show Detail

Oracle query to PostgreSQL conversion

Could you please help with some converted Oracle queries to postgreSQL queries, it is exists? I have an Oracle query which I want to adopt for postgreSQL, could you please help me with this? merge

Show Detail

How to get a postgresql string_to_array function working with Liquibase

The operative goal is to be able to compare an ID to a provided list passed in text form, e.g. "1,2,3,4,5." The thought was to simply use string_to_array to convert the input to an array ...

Show Detail

Conversion of Oracle Query to PostgreSQL

I am posting a part of my query which is in Oracle and it as follows: cast(from_tz(cast((Select max(d.startdate) from Public.result_slalom d where d.eventid = a.eventid ...

Show Detail

PostgreSQL string_to_array double quotes causing problem

In the below example tss should be equal to check given the argument of the function is 'WW123 (TestTest 20)®TEST50®®40®France™WW123 (TestTest 20)®TEst50®®40®Fra' but something goes wrong check bec.

Show Detail

Oracle function to PostgreSQL

I'm try to convert my Oracle function which contains SUBSTR() to PostgreSQL. Is there an equivalent function in PSQL? Thanks. I manage to find a Oracle Instr() conversion to PostgreSQL.

Show Detail

extract function conversion from oracle to postgresql

I am trying to change this oracle query to postgressql query but its not working for me: Oracle Query: select XMLAGG (XMLELEMENT (e, line_prefix || ',')).EXTRACT (' //text()'), ',') from in_line

Show Detail

Migrate query with START WITH and CONNECT BY PRIOR from oracle to postgresql

I am migrating a process from oracle to postgresql, and I am in another problem with the conversion of them. I have been researching how to migrate an oracle query, which has "START WITH" and "CON...

Show Detail