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
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
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
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
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
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|$)')
PL/SQL
Bhuvaneswari, February 19, 2024 - 4:29 pm UTC
How do I extract the amount which appears after the currency in the description column. 'EQUIVALENT' being the key word to identify in such lines.
Z24?MOUHAMED DIOUFSN SAINT LOUIS/ROC/SBGMD0000009033EQUIVALENT: XOF 228637UNITED NATIONS PENSION PYMT UNITE 20 NR 478 PARCELLES ASSAINIES, DAKAR
February 19, 2024 - 6:45 pm UTC
Extract what exactly?
PL/SQL
Bhuvaneswari, February 20, 2024 - 3:32 am UTC
How do I extract the amount 228637 which appears after the currency in the description column. 'EQUIVALENT' being the key word to identify in such lines.
Z24?MOUHAMED DIOUFSN SAINT LOUIS/ROC/SBGMD0000009033EQUIVALENT: XOF 228637UNITED NATIONS PENSION PYMT UNITE 20 NR 478 PARCELLES ASSAINIES, DAKAR
February 20, 2024 - 2:27 pm UTC
It depends on how standardized the format is, here's how I would approach this:
Substring to the part that begins EQUIVALENT (so you have EQUIVALENT: XOF 228637UNITED NATIONS ...)
Return the first set of numbers from this string - you can find these with the regex [0-9]+