CODEWITHSUNDEEP
sqloracle-database
user1793864
user1793864

Reputation: 185

SQL substr function

in my table one column contains data as below BMS/430301420-XN/0

I need to use substr function in oracle and output to be taken as 

430301420-XN

the one I used is as below

substr(buy_id,5),substr(substr(buy_id,5),instr(buy_id,'/',2))

but it is not working please help me

Upvotes: 2

Views: 306

Answers (5)

CompEng
CompEng

Reputation: 7376

my answer is :

select 
substr('BMS/430301420-XN/0',
      (instr('BMS/430301420-XN/0','/') +1),
      (instr('BMS/430301420-XN/0','/',(instr('BMS/430301420-XN/0','/')+1))-instr('BMS/430301420-XN/0','/')-1 ))
from dual

you can see this sample :

http://www.sqlfiddle.com/#!4/9eecb7/863/0

Upvotes: 0

Nagendra Nigade
Nagendra Nigade

Reputation: 876

This will also Work :D

select Column_Name as OLD , substr(''||to_char(Column_Name)||'',instr
(''||to_char(Column_Name)||'','/',1)+1,(instr(''||to_char(Column_Name)
||'','/',1,2)-instr(''||to_char(Column_Name)||'','/',1,1)-1)) as NEW from Table_Name;

Same Use Of substr and instr

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

With a combination of SUBSTR and INSTR:

SQL> WITH DATA AS(
  2  SELECT 'BMS/430301420-XN/0' str FROM dual UNION ALL
  3  SELECT 'BMSABC/430301420-XN/0' str FROM dual UNION ALL
  4  SELECT 'BMS/430301420-XN/012345' str FROM dual
  5  )
  6  SELECT str,
  7    SUBSTR(str, instr(str, '/', 1, 1)+1, instr(str, '/', 1, 2)
  8               -instr(str, '/', 1, 1)-1) new_str
  9  FROM DATA;

STR                     NEW_STR
----------------------- -----------------------
BMS/430301420-XN/0      430301420-XN
BMSABC/430301420-XN/0   430301420-XN
BMS/430301420-XN/012345 430301420-XN

SQL>

The above uses the logic to find the substring between the first and second occurrence of the /.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

Easiest way is a Regular Expression, find the string between the slashes but don't include them in the result:

regexp_substr(buy_id, '(?<=/).*(?=/)')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269573

If you know the format of the string and you always want to start on the fifth character and remove the last two, then:

select substr(str, 5, -2)

If you just want the part between the slashes, then use regexp_substr():

select replace(regexp_substr(str, '/.*/'), '/', '')

Upvotes: 3

Related Questions