CODEWITHSUNDEEP
sqloracle-database
Gaurav Kanodia
Gaurav Kanodia

Reputation: 87

How to extract the number from a string using Oracle?

I have a string as follows: first, last (123456) the expected result should be 123456. Could someone help me in which direction should I proceed using Oracle?

Upvotes: 0

Views: 1505

Answers (3)

MT0
MT0

Reputation: 168671

Find the last ( and get the sub-string after without the trailing ) and convert that to a number:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test ( str ) AS
SELECT 'first, last (123456)' FROM DUAL UNION ALL
SELECT 'john, doe (jr) (987654321)' FROM DUAL;

Query 1:

SELECT TO_NUMBER(
         TRIM(
           TRAILING ')' FROM
           SUBSTR(
             str,
             INSTR( str, '(', -1 ) + 1
           )
         )
       ) AS value
FROM   test

Results:

|     VALUE |
|-----------|
|    123456 |
| 987654321 |

Upvotes: 0

shonky linux user
shonky linux user

Reputation: 6428

Your question is ambiguous and needs clarification. Based on your comment it appears you want to select the six digits after the left bracket. You can use the Oracle instr function to find the position of a character in a string, and then feed that into the substr to select your text.

select substr(mycol, instr(mycol, '(') + 1, 6) from mytable

Or if there are a varying number of digits between the brackets:

select substr(mycol, instr(mycol, '(') + 1, instr(mycol, ')') - instr(mycol, '(') - 1) from mytable

Upvotes: 1

EdmCoff
EdmCoff

Reputation: 3596

It will depend on the actual pattern you care about (I assume "first" and "last" aren't literal hard-coded strings), but you will probably want to use regexp_substr.

For example, this matches anything between two brackets (which will work for your example), but you might need more sophisticated criteria if your actual examples have multiple brackets or something.

SELECT regexp_substr(COLUMN_NAME, '\(([^\)]*)\)', 1, 1, 'i', 1)
FROM TABLE_NAME

Upvotes: 1

Related Questions