0

I have a string value as a parameter and I need to parse it. My value is :

param := ('1234@5432@4567@8763');

I have to get 1234, 5432, 4567 and 8763 values partially. I will set these values different parameters.

How can I solve it with SQL?

Thanks,

jarlh
  • 42,561
  • 8
  • 45
  • 63
mkacar
  • 277
  • 1
  • 7
  • 17
  • split string? http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle – Prisoner Feb 08 '17 at 09:09
  • Do you need a single row or one row for each value? For a single row, a replace should be enough; for many rows, do some search for split string and you'll find many good answers – Aleksej Feb 08 '17 at 09:20
  • You can use a `FOR n IN LENGTH(param) LOOP` to loop trough every letter. – Tenzin Feb 08 '17 at 12:28

2 Answers2

0

Assuming that you are in PL/SQL and you need to split a value of a parameter or a variable into four variables, this could be a way:

declare
    param    varchar2(100);
    param1   varchar2(100);
    param2   varchar2(100);
    param3   varchar2(100);
    param4   varchar2(100);
begin    
    param := '1234@5432@4567@8763';
    --
    param1 := substr(param, 1, instr(param, '@', 1, 1)-1);
    param2 := substr(param, instr(param, '@', 1, 1) +1 , instr(param, '@', 1, 2) - instr(param, '@', 1, 1)-1);
    param3 := substr(param, instr(param, '@', 1, 2) +1 , instr(param, '@', 1, 3) - instr(param, '@', 1, 2)-1);
    param4 := substr(param, instr(param, '@', 1, 3) +1  );
    --
    dbms_output.put_line('Param1: ' || param1);
    dbms_output.put_line('Param2: ' || param2);
    dbms_output.put_line('Param3: ' || param3);
    dbms_output.put_line('Param4: ' || param4);
end;

With regular expressions, you can get the same result by searching the 1st, 2nd, ... occurrence of a string that is followed by a @ or by the end of the line ('$'); a better explanation of this approach is described in the link gave by Gary_W in his comment

...
param1 := regexp_substr(param, '(.*?)(@|$)', 1, 1, '', 1 );
param2 := regexp_substr(param, '(.*?)(@|$)', 1, 2, '', 1 );
param3 := regexp_substr(param, '(.*?)(@|$)', 1, 3, '', 1 );
param4 := regexp_substr(param, '(.*?)(@|$)', 1, 4, '', 1 );
...
Aleksej
  • 22,443
  • 5
  • 33
  • 38
  • 1
    The regex of the form `'[^@]+'` commonly used to parse lists does not work if there is a NULL element in the list and will return an incorrect value. See here for more info on why it should be avoided: http://stackoverflow.com/a/31464699/2543416. Please consider this form instead: `regexp_substr(param, '(.*?)(@|$)', 1, 1, NULL, 1)` where the 4th argument is the index of the element you want to return. – Gary_W Feb 08 '17 at 15:00
0
select level, regexp_substr(a,'\d+',1,level)
from(select '1234@5432@4567@8763' a from dual)
connect by level <= regexp_count(a,'@') + 1
Ivan Bryzzhin
  • 2,009
  • 21
  • 27
  • 1
    I'm afraid this does not work if there is a NULL list element. Try it will this list to test: `'1234@@4567@8763'`. Consider using this regexp instead: `regexp_substr(a,'(.*?)(@|$)',1,level, null, 1)` – Gary_W Feb 08 '17 at 14:51