Home » SQL & PL/SQL » SQL & PL/SQL » Extracting number (12c)
Extracting number [message #663943] Tue, 27 June 2017 08:00 Go to next message
m.abdulhaq
Messages: 233
Registered: April 2013
Location: Ajman
Senior Member
i want to remove the characters from the following values in the field.

WO_NO=4^
WO_NO=13^
WO_NO=112^

I want to get the result as

4
13
112.
Re: Extracting number [message #663944 is a reply to message #663943] Tue, 27 June 2017 08:04 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
m.abdulhaq wrote on Tue, 27 June 2017 06:00
i want to remove the characters from the following values in the field.

WO_NO=4^
WO_NO=13^
WO_NO=112^

I want to get the result as

4
13
112.
REPLACE all non-numeric digits with NULL
Re: Extracting number [message #663945 is a reply to message #663944] Tue, 27 June 2017 08:15 Go to previous messageGo to next message
cookiemonster
Messages: 12959
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or if the number always comes between = and ^ then use instr to get the position of those characters and substr to get the number between.
Re: Extracting number [message #663946 is a reply to message #663944] Tue, 27 June 2017 08:48 Go to previous messageGo to next message
m.abdulhaq
Messages: 233
Registered: April 2013
Location: Ajman
Senior Member
yes i did it using regexp_replace.


select regexp_replace(key_ref, '[^0-9]', ''), key_ref from APPROVAL_ROUTING  WHERE LU_NAME='ActiveSeparate'

Re: Extracting number [message #663947 is a reply to message #663946] Tue, 27 June 2017 09:51 Go to previous messageGo to next message
cookiemonster
Messages: 12959
Registered: September 2008
Location: Rainy Manchester
Senior Member
instr/substr is generally more performant that regexp
Re: Extracting number [message #663972 is a reply to message #663947] Thu, 29 June 2017 06:09 Go to previous messageGo to next message
m.abdulhaq
Messages: 233
Registered: April 2013
Location: Ajman
Senior Member
thanks blackswan and cookiemonster.
Re: Extracting number [message #663991 is a reply to message #663943] Sat, 01 July 2017 11:18 Go to previous messageGo to next message
bharath511
Messages: 5
Registered: August 2011
Location: bangalore
Junior Member
Hi,

Regular express will consider ^ as initial character of given word. Use escape character along with ^

select regexp_replace('4^','\^','') from dual ;

Re: Extracting number [message #663992 is a reply to message #663991] Sat, 01 July 2017 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Regular express will consider ^ as initial character of given word.
This is wrong, ^ is the beginning of the line or string (depending on other parameters)

SQL> select regexp_replace('foo bar','^bar','failed') from dual;
REGEXP_
-------
foo bar

And as with your previous reply:
Michel Cadot wrote on Fri, 26 August 2011 18:26
@bharath511

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...
Re: Extracting number [message #664026 is a reply to message #663992] Mon, 03 July 2017 13:26 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
select regexp_substr(src_fied, '\d+') as res from src_table
Re: Extracting number [message #664028 is a reply to message #664026] Mon, 03 July 2017 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Will not be more efficient than SUBSTR/INSTR cookiemonster mentioned.

Re: Extracting number [message #664031 is a reply to message #664028] Mon, 03 July 2017 23:40 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
Maybe, but it's better than use regexp_replace imho
Re: Extracting number [message #664033 is a reply to message #664031] Tue, 04 July 2017 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What does "better" mean?
As I said it is no "better" for performances.

Re: Extracting number [message #664037 is a reply to message #664033] Tue, 04 July 2017 01:06 Go to previous message
bbob
Messages: 21
Registered: July 2017
Junior Member
"Better" mean more clearly and less data manipulation required (in comparison with regexp_replace of course)

About performance...

with
  src as (select 'WO_NO=112^' as str from dual connect by level <= 2000000)
select count(res)
  from (
    select
      regexp_replace(str, '[^0-9]*', '') as res
      --regexp_substr(str, '\d+') as res
      --substr(str, 7, instr(str, '^') - 7) as res
      from src);

regexp_replace takes about 10 seconds
regexp_substr - 3 seconds
substr - 1 second
Previous Topic: Moving data from one table to another
Next Topic: Recursive select
Goto Forum:
  


Current Time: Fri Dec 15 03:55:21 CST 2017

Total time taken to generate the page: 0.06629 seconds