CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldeveloper
Ankit
Ankit

Reputation: 129

Usage of Substring and Instirng in oracle

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

Upvotes: 0

Views: 80

Answers (2)

San
San

Reputation: 4538

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;

Upvotes: 3

arunb2w
arunb2w

Reputation: 1196

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

Upvotes: 0

Related Questions