sql string operation - something like strtoken

I have a long string that holds data1$data2$data3$data4$ and so on.
I need to write some utility function that will extract the data part. The string can hold any number of datas concatenated by $

Any ideas on what str function I can use.

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

sdstuberConnect With a Mentor Commented:
try regexp_substr

REGEXP_SUBSTR(str, '[^$]*\$',1,n)

where "str" is your data string
and "n" is the 1-based index of the data substring you want.

REGEXP_SUBSTR(str, '[^$]*\$',1,1)
would return "data1$"

REGEXP_SUBSTR(str, '[^$]*\$',1,4)
would return "data4$"

See if you can play with this code:
type t_vector is table of varchar2(10) index by binary_integer;
v_value t_vector;
v_index_next pls_integer := 1;
v_index_pipe pls_integer := 1;
i pls_integer := 0;
param_to_split varchar2(100) := 'data1$data2$data3$data4$data6$data8$data7$';


 v_index_pipe := instr(param_to_split, '$', 1, 1);

 if v_index_pipe > 0 then

  while v_index_pipe > 0 loop

   i := i + 1;
   v_value(i) := substr(param_to_split, v_index_next, v_index_pipe - v_index_next);
   v_index_next := v_index_pipe + 1;
   v_index_pipe := instr(param_to_split, '$', v_index_next, 1);

  end loop;

 end if;


Open in new window

if your final substring might not have a trailing "$"

then try this... (note the "?" character)

REGEXP_SUBSTR(str, '[^$]*\$?', 1, n)
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

slightwv (䄆 Netminder) Commented:
I need to ask what the final result is here.

Normally when I see questions like this it is a small part of a larger issue.

If you have a file with delimited text and you are looking for a way to load it, there are much better ways.

If the file is on the database server: External Tables.

If the file is on the client and you installed the utilities:  SQL*Loader (sqlldr).
Naveen KumarProduction Manager / Application Support ManagerCommented:
can you try :

with v1 as (
select 'data1$data2$data3$data4$' col from dual )
select regexp_substr(v1.col,'[^$]+',1,level,'i') split_values from dual, v1
connect by level <= length(v1.col) - length(replace(v1.col,'$'))

We use regexp_substr(..) string function to get that done.

regexp_substr was suggested already in the very first post.

also, the 'i'  case-insensitive option isn't really necessary here.  The pattern is already case insensitive.  It doesn't hurt to have it, but it doesn't add any functionality to what has already been suggested
All Courses

From novice to tech pro — start learning today.