convert string into rows with SQL

Question

I have a string, like:

'one,two,three'

and I want to convert it in rows and use it into IN clause in an SQL:

one
two
three

I tried something like :

SELECT column_value 
  FROM XMLTable('"one","two","three"');

and it worked fine but in a join condition it fails.

SELECT 1 
  FROM dual 
 WHERE 'one' IN (SELECT column_value 
                   FROM XMLTable('"one","two","three"'));

it gaves me the error:

ORA-00932: inconsistent datatypes: expected - got CHAR
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

Can anyone help me on this, please?

NOTE: I would like not use PLSQL

Using to_CHAT, will surely not work. Still not work using TO_CHAR. — mikcutu, Jun 21 '22 at 05:02

score 1 · Answer 1 · answered Jun 20 '22 at 16:47

You do not need XML to split the string and can use simple (fast) string functions:

WITH data (value) AS (
  SELECT 'one,two,three' FROM DUAL
),
bounds (value, spos, epos) AS (
  SELECT value, 1, INSTR(value, ',', 1)
  FROM   data
UNION ALL
  SELECT value, epos + 1, INSTR(value, ',', epos + 1)
  FROM   bounds
  WHERE  epos > 0
)
SEARCH DEPTH FIRST BY value SET order_id
SELECT CASE epos
       WHEN 0
       THEN SUBSTR(value, spos)
       ELSE SUBSTR(value, spos, epos-spos)
       END as item
FROM   bounds;

Which outputs:

ITEM

one

two

three

However

In your case you have an XY-problem and you DO NOT NEED to split the string as you can use LIKE to match the search string against a sub-string of your list:

SELECT 1
FROM   dual
WHERE  ',' || :your_list || ',' LIKE '%,' || :search_value || ',%';

or with hardcoded strings:

SELECT 1
FROM   dual
WHERE  ',' || 'one,two,three' || ',' LIKE '%,' || 'one' || ',%';

db<>fiddle here

score 1 · Answer 2 · answered Jun 20 '22 at 21:06

Query that raised an error - if rewritten to this - works:

SQL> select 1
  2  from dual
  3  where 'one' in (select regexp_substr('one,two,three', '[^,]+', 1, level)
  4                  from dual
  5                  connect by level <= regexp_count('one,two,three', ',') + 1
  6                 );

         1
----------
         1

SQL>

Subquery (that uses regexp_substr) splits a comma-separated list of values ('one,two,three') into rows.

Alternatively, if you use Oracle Apex (or have it installed in your database), you can simplify it by utilizing apex_string.split:

SQL> select 1
  2  from dual
  3  where 'one' in (select * from apex_string.split('one,two,three', ','));

         1
----------
         1

SQL>

Barbaros Özhan · Accepted Answer · 2022-06-20T22:03:01.290

1

What you need is nothing but just casting a CLOB value to a [VAR]CHAR[2] data type such as

SELECT 1 
  FROM dual 
 WHERE 'one' IN (SELECT CAST(column_value AS VARCHAR2(20))
                   FROM XMLTable('"one","two","three"'))

1
---
  1

in order to make it comparable with a literal(such as 'one').

Moreover, CAST might be replaceable with XMLCast as well.

edited Jun 20 '22 at 22:03

answered Jun 20 '22 at 21:52

Barbaros Özhan

59,113
10
31
55

score 0 · Answer 4 · answered Jun 20 '22 at 17:53

You could use REGEXP_COUNT to find your word in the list.

This would be faster than converting

But read this canonical thread Is storing a delimited list in a database column really that bad?

SELECT
    CASE WHEN REGEXP_COUNT('one,two,three', '^([^,]+,)*one(,[^,]+)*$', 1, 'i') > 0
        THEN 1 ELSE 0
    END as cnt
FROM DUAL;

| CNT |
| --: |
|   1 |

db<>fiddle here

convert string into rows with SQL

4 Answers4

However