0

I have a query:

       Select SUBSTR(concat_prod_id,1,(INSTR(concat_prod_id,',',1,1)-1)) AS Test
       from        dims_doc_master
       where pyid='D-122663'

Now Concat_pord_id has values like '121,122,123' and the number of values within this string can vary from 1 to any number. The above query returns only 121, that's it. What i wanted is this query to return a result 3 different rows:

1. 121
2. 122
3. 123.

Please suggest

Ankit
  • 129
  • 7
  • 17
  • possible duplicate of [How to best split csv strings in oracle 9i](http://stackoverflow.com/questions/1089508/how-to-best-split-csv-strings-in-oracle-9i) – Florin Ghita Jan 27 '14 at 15:10
  • This post doesnt answer the query i raised. The post Florin mentioned only parses a string. I already have a comma separated string, i want to separate it into different rows. SO if my column has three values separated by comma i want three different rows/ – Ankit Jan 27 '14 at 15:28
  • In short , you want to tokenize your comma seprated value string and convert into rows? – Maheswaran Ravisankar Jan 27 '14 at 15:38
  • Yes Maheswaran, thats what i am looking for. – Ankit Jan 27 '14 at 15:41
  • Don't you consider having a function ? Will you have only one such String at a time? – Maheswaran Ravisankar Jan 27 '14 at 15:45
  • Yeah i need a function, this string is variable and can have any number of values. – Ankit Jan 27 '14 at 15:49

2 Answers2

3

Use this query, this works for any number of rows:

WITH tab(str) AS (SELECT '121,122,123' FROM dual UNION ALL
                  SELECT '221,222,223' FROM dual UNION ALL
                  SELECT '321,322,323' FROM dual)
------------
---End of data
------------
SELECT REGEXP_SUBSTR (str,'[^,]+',1,LEVEL) txt
  FROM tab
CONNECT BY REGEXP_SUBSTR (str,'[^,]+',1,LEVEL) IS NOT NULL
   AND PRIOR str = str
   AND PRIOR sys_guid() IS NOT NULL;

Ouput:

| TXT |
|-----|
| 121 |
| 122 |
| 123 |
| 221 |
| 222 |
| 223 |
| 321 |
| 322 |
| 323 |

Your query:

SELECT REGEXP_SUBSTR(concat_prod_id,'[^,]+',1,LEVEL) AS test
  FROM dims_doc_master
 WHERE pyid = 'D-122663'
CONNECT BY REGEXP_SUBSTR(concat_prod_id,'[^,]+',1,LEVEL) IS NOT NULL
   AND PRIOR concat_prod_id = concat_prod_id
   AND PRIOR sys_guid() IS NOT NULL;
San
  • 4,508
  • 1
  • 13
  • 19
0

Try this pls

SELECT REGEXP_SUBSTR ('121,122,123','[^,]+',1,LEVEL) txt
FROM DUAL
CONNECT BY LEVEL <=
LENGTH ('121,122,123') - LENGTH (REPLACE ('121,122,123',',')) + 1
arunb2w
  • 1,196
  • 9
  • 28