OraFAQ Forum: SQL & PL/SQL » Extracting number

Home » SQL & PL/SQL » SQL & PL/SQL » Extracting number (12c)

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

Extracting number [message #663943]

Tue, 27 June 2017 08:00

m.abdulhaq
Messages: 253
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.

Report message to a moderator

Re: Extracting number [message #663944 is a reply to message #663943]

Tue, 27 June 2017 08:04

BlackSwan
Messages: 26763
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

Report message to a moderator

Re: Extracting number [message #663945 is a reply to message #663944]

Tue, 27 June 2017 08:15

cookiemonster
Messages: 13894
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.

Report message to a moderator

Re: Extracting number [message #663946 is a reply to message #663944]

Tue, 27 June 2017 08:48

m.abdulhaq
Messages: 253
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'

Report message to a moderator

Re: Extracting number [message #663947 is a reply to message #663946]

Tue, 27 June 2017 09:51

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

Senior Member

instr/substr is generally more performant that regexp

Report message to a moderator

Re: Extracting number [message #663972 is a reply to message #663947]

Thu, 29 June 2017 06:09

m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman

Senior Member

thanks blackswan and cookiemonster.

Report message to a moderator

Re: Extracting number [message #663991 is a reply to message #663943]

Sat, 01 July 2017 11:18

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 ;

Report message to a moderator

Re: Extracting number [message #663992 is a reply to message #663991]

Sat, 01 July 2017 14:06

Michel Cadot
Messages: 67817
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.
...

Report message to a moderator

Re: Extracting number [message #664026 is a reply to message #663992]

Mon, 03 July 2017 13:26

bbob
Messages: 21
Registered: July 2017

Junior Member

select regexp_substr(src_fied, '\d+') as res from src_table

Report message to a moderator

Re: Extracting number [message #664028 is a reply to message #664026]

Mon, 03 July 2017 14:12

Michel Cadot
Messages: 67817
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Extracting number [message #664031 is a reply to message #664028]

Mon, 03 July 2017 23:40

bbob
Messages: 21
Registered: July 2017

Junior Member

Maybe, but it's better than use regexp_replace imho

Report message to a moderator

Re: Extracting number [message #664033 is a reply to message #664031]

Tue, 04 July 2017 00:27

Michel Cadot
Messages: 67817
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Extracting number [message #664037 is a reply to message #664033]

Tue, 04 July 2017 01:06

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

Report message to a moderator

Previous Topic:	Moving data from one table to another
Next Topic:	Recursive select

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 23 01:01:23 CDT 2021