My previous post about selecting from a comma-separated string has been made redundant by the updated APEX_STRING package.
Nowadays, to select values from a comma-separated string you can simply use the SPLIT function of the APEX_STRING package. This method has the advantage of also working with other delimiters apart from commas and of working with non-numeric values
The APEX_STRING.SPLIT function accepts the following three arguments
Nowadays, to select values from a comma-separated string you can simply use the SPLIT function of the APEX_STRING package. This method has the advantage of also working with other delimiters apart from commas and of working with non-numeric values
The APEX_STRING.SPLIT function accepts the following three arguments
- p_str - the string to be split
- p_sep - the separator (optional parameter)
the default is a line feed - p_limit - the maximum number of splits (optional parameter)
the default is null i.e. ignore this parameter
-- comma separated list
select column_value as id
from table(apex_string.split('123,456,789,012,234,567,890',','));
ID
---------
123
456
789
012
234
567
890
7 rows selected.
-- hashtag separated list
select column_value as my_value
from table(apex_string.split('cat#dog#cow#horse#dolphin#elephant#bigfoot','#'));
MY_VALUE
---------
cat
dog
cow
horse
dolphin
elephant
bigfoot
7 rows selected.
No comments:
Post a Comment