OraFAQ Forum: SQL & PL/SQL » substring instr query

Home » SQL & PL/SQL » SQL & PL/SQL » substring instr query (SQL Oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

substring instr query [message #625963]

Fri, 17 October 2014 04:18

incrediblek
Messages: 5
Registered: March 2013
Location: UK

Junior Member

Hi,

I was wondering if there is a better or shorter way of extracting the numbers after the last hyphen (-) in a column. The numbers after the last hyphen are minimum 1 digit and a maximum of 3 digits.

Here are some examples of the data in the column.

original data (ID)	desired result
84/12674-1		1
P50170-273		273
B03-05789-13		13
45/14569-100		100	
Z16537-9		9

This is what I have come up with:

select replace(substr(client_db.ID,INSTR(client_db.ID,'-',-1,1),4),'-',null)
from client_db

Is there a better way of getting the same results?

Thanks,
K

[Updated on: Fri, 17 October 2014 04:20]

Report message to a moderator

Re: substring instr query [message #625964 is a reply to message #625963]

Fri, 17 October 2014 04:44

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well the replace isn't necessary:

SQL> WITH DATA AS (SELECT '84/12674-1' a FROM dual
  2  UNION ALL SELECT 'P50170-273' a FROM dual
  3  UNION ALL SELECT 'B03-05789-13' a FROM dual
  4  UNION ALL SELECT '45/14569-100' a FROM dual
  5  UNION ALL SELECT 'Z16537-9' a FROM dual)
  6  select substr(a,INSTR(a,'-',-1,1) + 1), a
  7  from DATA;
 
SUBSTR(A,INSTR(A,'-',-1,1)+1)        A
------------------------------------ ------------
1                                    84/12674-1
273                                  P50170-273
13                                   B03-05789-13
100                                  45/14569-100
9                                    Z16537-9
 
SQL>

Other ways will require regexps, I imagine someone else will suggest how.

Report message to a moderator

Re: substring instr query [message #625966 is a reply to message #625963]

Fri, 17 October 2014 04:46

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Using SUBSTR + INSTR :

SQL> WITH DATA AS(
  2  SELECT '84/12674-1' str FROM dual UNION ALL
  3  SELECT 'P50170-273' FROM dual UNION ALL
  4  SELECT 'B03-05789-13' FROM dual UNION ALL
  5  SELECT '45/14569-100' FROM dual UNION ALL
  6  SELECT 'Z16537-9' FROM dual
  7  )
  8  SELECT str, substr(str, instr(str, '-', -1, 1)+1) sub_str
  9  FROM DATA
 10  /

STR          SUB_STR
------------ ------------
84/12674-1   1
P50170-273   273
B03-05789-13 13
45/14569-100 100
Z16537-9     9

SQL>

Using REGEXP_SUBSTR :

SQL> WITH DATA AS(
  2  SELECT '84/12674-1' str FROM dual UNION ALL
  3  SELECT 'P50170-273' FROM dual UNION ALL
  4  SELECT 'B03-05789-13' FROM dual UNION ALL
  5  SELECT '45/14569-100' FROM dual UNION ALL
  6  SELECT 'Z16537-9' FROM dual
  7  )
  8  SELECT str, regexp_substr(str, '\d+$') sub_str
  9  FROM DATA
 10  /

STR          SUB_STR
------------ ------------
84/12674-1   1
P50170-273   273
B03-05789-13 13
45/14569-100 100
Z16537-9     9

SQL>

[Updated on: Fri, 17 October 2014 05:10]

Report message to a moderator

Previous Topic:	oracle
Next Topic:	How to calculate age between timestamp ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 06:17:17 CDT 2024