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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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$"


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.