Home » SQL & PL/SQL » SQL & PL/SQL » extract string using regex (10g)
extract string using regex [message #644858] Thu, 19 November 2015 02:06 Go to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
i want to extract the numbers before mm and after last 'x' from the following string using regex.




'BUS1078x622x25x50x5213mm, Gr. 50'
'BUS2000x400x30x30x9020mm ,astm'
'BUS2000x400x30x30x90200mm',a36



--required output is


5213
9020
90200

Re: extract string using regex [message #644864 is a reply to message #644858] Thu, 19 November 2015 03:30 Go to previous messageGo to next message
prabhatlnct2008
Messages: 3
Registered: November 2015
Location: india
Junior Member
WITH qry AS
(SELECT 'BUS1078x622x25x50x5213mm, Gr. 50' as text FROM dual UNION ALL
SELECT 'BUS2000x400x30x30x9020mm ,astm' FROM dual UNION ALL
SELECT 'BUS2000x400x30x30x90200mm' FROM dual )

select regexp_replace(regexp_substr(text , 'x\d+mm') , '[xm]')
from qry
Re: extract string using regex [message #644866 is a reply to message #644864] Thu, 19 November 2015 03:55 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks prabhat
Re: extract string using regex [message #644867 is a reply to message #644866] Thu, 19 November 2015 03:59 Go to previous messageGo to next message
prabhatlnct2008
Messages: 3
Registered: November 2015
Location: india
Junior Member
welcome Smile
Re: extract string using regex [message #644887 is a reply to message #644864] Thu, 19 November 2015 06:09 Go to previous message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or simpler (assuming 11G or higher):

WITH qry AS 
(SELECT 'BUS1078x622x25x50x5213mm, Gr. 50' as text FROM dual UNION ALL 
SELECT 'BUS2000x400x30x30x9020mm ,astm' FROM dual UNION ALL 
SELECT 'BUS2000x400x30x30x90200mm' FROM dual )
select regexp_substr(text,'x(\d+)mm',1,1,null,1)
from qry
/


SY.
Previous Topic: How to display multiple records in a single row
Next Topic: Basic sorting concept
Goto Forum:
  


Current Time: Tue Mar 19 01:19:54 CDT 2024