OraFAQ Forum: SQL & PL/SQL » How to use function to extract 1 or 2 digit number from a string

Home » SQL & PL/SQL » SQL & PL/SQL » How to use function to extract 1 or 2 digit number from a string

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

How to use function to extract 1 or 2 digit number from a string [message #648848]

Fri, 04 March 2016 14:33

blyzz
Messages: 10
Registered: October 2015

Junior Member

I am trying to use str function to extract number from a string
The String always starts with 1 or 2 digit number and then "" and alphabetic characters:
Ex: 10-high or 6-low
The issue is that if I use Substr(string 1,2) then I am getting 10 for first string but 6- for second one but I am only interested in number 10 or 6
Is there any other function that I can use?
How can I do this?

Thank you,
Blyzzard

Report message to a moderator

Re: How to use function to extract 1 or 2 digit number from a string [message #648856 is a reply to message #648848]

Fri, 04 March 2016 15:28

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

If your data always has a hyphen after the number, then you could use that to identify where the number ends, as shown below.

SCOTT@orcl> with test_tab as
  2    (select '10-high' test_data from dual union all
  3  	select '6-low' from dual)
  4  select substr (test_data, 1, instr (test_data, '-') - 1)
  5  from   test_tab
  6  /

SUBSTR(TEST_DATA,1,INSTR(TES
----------------------------
10
6

2 rows selected.

Report message to a moderator

Re: How to use function to extract 1 or 2 digit number from a string [message #648859 is a reply to message #648848]

Sat, 05 March 2016 01:06

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

Senior Member
Account Moderator

Or, using regular expression function.

SQL> with test_tab as
  2    (select '10-high' test_data from dual union all
  3    select '6-low' from dual)
  4  select test_data, regexp_substr(test_data,'^\d+') num
  5  from test_tab
  6  /
TEST_DA NUM
------- -------
10-high 10
6-low   6

Report message to a moderator

Re: How to use function to extract 1 or 2 digit number from a string [message #648928 is a reply to message #648859]

Mon, 07 March 2016 09:42

blyzz
Messages: 10
Registered: October 2015

Junior Member

Thank you so much.
Both of them worked fine.

Blyzz

Report message to a moderator

Re: How to use function to extract 1 or 2 digit number from a string [message #648929 is a reply to message #648928]

Mon, 07 March 2016 16:07

Bill B
Messages: 1971
Registered: December 2004

Senior Member

If you have a large amount of records to scan the instr is a little faster then the regular expressions. If it is just a few thousand or less, then don't worry about it.

Report message to a moderator

Previous Topic:	Compile an object by oracle command for first time
Next Topic:	Issue with Sys_context

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 06:22:21 CDT 2024