CODEWITHSUNDEEP
sqlplsql
user2106410
user2106410

Reputation: 69

Substring Function

The output from a column is like this 'Arpit-Bansal-Doctor-ALC FU JP-111DC'.I want to extract the data which is between the - - that is for example Bansal.

Please advice which function to use and how to use.

Upvotes: 0

Views: 109

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

Another method:

select regexp_substr('Arpit-Bansal-Doctor-ALC FU JP-111DC', '[^-]+')
from dual;

Arpit

select regexp_substr('Arpit-Bansal-Doctor-ALC FU JP-111DC', '[^-]+', 1, 2)
from dual;

Bansal

select regexp_substr('Arpit-Bansal-Doctor-ALC FU JP-111DC', '[^-]+', 1, 3)
from dual;

Doctor

select regexp_substr('Arpit-Bansal-Doctor-ALC FU JP-111DC', '[^-]+', 1, 4)
from dual;

ALC FU JP

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132620

You can do it using the built-in functions INSTR and SUBSTR like this:

select substr(col,instr(col,'-')+1,instr(col,'-',1,2)-instr(col,'-')-1) 
from (select 'Arpit-Bansal-Doctor-ALC FU JP-111DC' col from dual);

The INSTR function calls are used to find the first and second hyphen, and then SUBSTR is used to get the string between them.

This is quite cumbersome, so if you want all 5 values separated out and you are in PL/SQL you can use apex_util.string_to_table to separate them:

declare
   array apex_application_global.vc_arr2;
begin
   array := apex_util.string_to_table ('Arpit-Bansal-Doctor-ALC FU JP-111DC', '-');
   for i in 1..array.count loop
       dbms_output.put_Line ('Part' || i || ' is '||array(i));
   end loop;
end;

Upvotes: 1

Related Questions