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.

Thanks
happylife1234Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

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


0
 
jameso99Commented:
See if you can play with this code:
declare
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$';

begin

 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);
   dbms_output.put_line(v_value(i));

  end loop;

 end if;

end;

Open in new window

0
 
sdstuberCommented:
if your final substring might not have a trailing "$"

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

REGEXP_SUBSTR(str, '[^$]*\$?', 1, n)
0
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).
0
 
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.
0
 
sdstuberCommented:
nav_kum_v,

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
0
All Courses

From novice to tech pro — start learning today.