Using to_char to convert number or date with xmltable

Question

I have this query like this:

select to_number(column_value) as IDs from xmltable('1,2,3,4,12.33,testtest');

Is it possible to use to_char function to convert numbers and strings inside xmltable to rows? So my output would be like this:

output
1
2
3
4
12,33
testtest

Answer 1

With your current list of values you'll get:

select to_number(column_value) as IDs from xmltable('1,2,3,4,12.33,testtest');

ORA-19228: XPST0008 - undeclared identifier: prefix '.', local-name ''

It would work with just numeric values:

select to_number(column_value) as IDs from xmltable('1,2,3,4,12.33');

IDS
1
2
3
4
12.33

And it would work if the string value looked like an identifier, i.e. if you were able to enclose it in double quotes within your value list:

select to_number(column_value) as IDs from xmltable('1,2,3,4,12.33,"testtest"');

ORA-01722: unable to convert string value containing 't' to a number: 

... except of course you can't convert the string 'testtest' to a number.

But you can treat them all as strings instead:

select column_value as IDs from xmltable('1,2,3,4,12.33,"testtest"');

IDS
1
2
3
4
12.33
testtest

fiddle

If you're working with a list of values you're being passed then adding the double quotes around strings is feasible but probably not worth the effort, and a normal hierarchical query or recursive CTE approach to splitting the CSV list into values is likely to be more appropriate.

Answer 2

I am not sure about xmltable. But, if you can extract the string, you can use REGEXP_SUBSTR AND LEVEL. Here is the sample code:

SELECT REGEXP_SUBSTR('1,2,3,4,12.33,testtest', '[^,]+', 1, LEVEL) AS IDs
FROM dual
CONNECT BY REGEXP_SUBSTR('1,2,3,4,12.33,testtest', '[^,]+', 1, LEVEL) IS NOT NULL;

Here is sample output: