Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, rakesh.

Asked: September 01, 2017 - 11:51 am UTC

Last updated: January 24, 2022 - 2:52 am UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

I wanted to extract all the numbers from this string '12gfsda@3fg,f' and want to display.
Like for example i want 123 to be extracted from 12gfsda@3fg,f and display only 123.

and Chris said...

You could use a regular expression which removes all non-numbers from the input:

select regexp_replace('1234bsdfs3@23##PU', '[^0-9]', '') nums
from   dual;

NUMS     
1234323  

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 02, 2017 - 8:04 am UTC

If you need both an individually means write an sql statement like this....select regexp_replace('colname' , '[^0-9]', '')as num from dual
Union
select regexp_replace('colname' , '[^a-zA-Z]', '')as num from dual

A reader, September 02, 2017 - 8:04 am UTC

If you need both an individually means write an sql statement like this....select regexp_replace('colname' , '[^0-9]', '')as num from dual
Union
select regexp_replace('colname' , '[^a-zA-Z]', '')as char from dual

A reader, August 08, 2018 - 5:03 pm UTC

Can we do it like the below.


NUMS
1234
23
Chris Saxon
August 09, 2018 - 9:56 am UTC

Do what?

John Keymer, August 09, 2018 - 3:12 pm UTC

with a as (
select '1234bsdfs3@23##PU' a from dual)
select regexp_substr(a,'[0-9]+',1,level) nums from a
connect by level <= regexp_count(a.a,'[^0-9]+')

Non regex solution for extracting digits ...

cd, August 10, 2018 - 9:08 am UTC

SELECT translate('1234bsdfs3@23##PU', '0123456789' || translate('1234bsdfs3@23##PU','x123456789','x'),'0123456789') nums
   FROM dual  ;

NUMS   
-------
1234323

Chris Saxon
March 05, 2020 - 3:26 am UTC

Thanks for sharing..

MODEL clause solution for extracting numbers

cd, August 10, 2018 - 9:15 am UTC

WITH t AS (SELECT '1234bsdfs3@23##PU' str
             FROM dual)
SELECT nums
  FROM t
  MODEL
  PARTITION BY (rownum rn)
  DIMENSION BY (1 i)
  MEASURES (str, regexp_count(str, '[0-9]+') cnt, str nums)
  RULES (nums[for i from 1 to cnt[1] increment 1] = regexp_substr(str[1], '[0-9]+', 1, CV(i))
  )

NUMS             
-----------------
1234
3
23

Chris Saxon
August 10, 2018 - 9:25 am UTC

OK, now that's just getting silly ;)

Thanks anyway!

A reader, September 04, 2018 - 10:00 am UTC


Thanks

Ulzii, February 02, 2019 - 3:38 am UTC


replace

ram, November 30, 2019 - 12:36 pm UTC

sir,this name 123#$@20vdah use any substr,instr,replace,lpad,rpad,
,translate functions
Chris Saxon
December 05, 2019 - 9:32 am UTC

To do what exactly?

Find only one number

Vikas Sharma, January 22, 2022 - 2:29 pm UTC

Hi,
I have a table with 2 columns land data like following
ID Value
----- --------
265 153, 1540
266 1594, 54, 900
267 154

Using SQL i want to find the Rows where value has 154 So the result should be only last row here in above example

ID Value
-------------
267 154

please suggest the best way of doing it using SQL

Best regards

Vikas Sharma
Connor McDonald
January 24, 2022 - 2:52 am UTC

select *
from ...
where instr(value,'154') > 0

but a data model like that always looks like ongoing trouble to me

154

not 1540, January 27, 2022 - 10:52 am UTC

regexp_like(s, '(^|\W)154(\W|$)')

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.