14 November 2018

An updated and better method for selecting values from a string using the APEX_STRING package

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

  • 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