Oracle: Extract number from String

Question

I've reviewed this question and I'm wondering my output seems to be a little skewed.

From my understanding the REGEXP_REPLACE method, takes a string that you want to replace content with, followed by a pattern to match, then anything that does not match that pattern is replaced with the substitution param.

I've written the following function to extract distance from a text field, in which a spatial query will be performed on the result.

CREATE OR REPLACE FUNCTION extract_distance
(
    p_search_string   VARCHAR2
) 
    RETURN VARCHAR2
IS
    l_distance        VARCHAR2(25);
BEGIN

    SELECT REGEXP_REPLACE(UPPER(p_search_string), '(([0-9]{0,4}) ?MILES)', '') 
    INTO l_distance FROM SYS.DUAL;

    RETURN l_distance;
END extract_distance;

When I run this in a block to test:

DECLARE
    l_output VARCHAR2(25);
BEGIN
    l_output := extract_distance('Stores selling COD4 in 400 Miles');
    DBMS_OUTPUT.PUT_LINE(l_output);     
END;

I'd expect the output 400 miles but in-fact I get Stores selling COD4 in. Where have I gone wrong?

I don't know Oracle, but it looks like your code is removing the Regex pattern (i.e. Replace the pattern with ''). I think you need to negate the Regex, i.e replace the part that is not the pattern with the empty string — Sparky
– Sparky, Commented Dec 6, 2014 at 15:55
Thanks Sparky, those were my thoughts too, however in the question I referenced the opposite behaviour is demonstrated. Maybe I should write a subsitution expression to override the first part of the string. — Halfpint
– Halfpint, Commented Dec 6, 2014 at 15:57
@Alex: In that question the regular expression is negated. [^0-9] doesn't match a digit, it matches a character that is not a digit. The ^ at the beginning of the set makes it a negative set. — Guffa
– Guffa, Commented Dec 6, 2014 at 16:03
@Guffa ah that is very interesting - Regex isnt my best area, so that would make sense — Halfpint
– Halfpint, Commented Dec 6, 2014 at 16:09
Performance wise, SUBSTR and INSTR approach would be efficient and less resource consuming than REGEXP. — Lalit Kumar B
– Lalit Kumar B, Commented Dec 7, 2014 at 3:05

Abecee · Accepted Answer · 2014-12-06 16:13:05Z

3

"REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string." from Oracle docu

You could use, e.g.,
SELECT REGEXP_REPLACE('Stores selling COD4 in 400 Miles', '^.*?(\d+ ?MILES).*$', '\1', 1, 0, 'i') FROM DUAL;

or alternatively
SELECT REGEXP_SUBSTR('Stores selling COD4 in 400 Miles', '(\d+ ?MILES)', 1, 1, 'i') FROM DUAL;

edited Dec 6, 2014 at 16:13

answered Dec 6, 2014 at 15:57

Abecee

2,4032 gold badges14 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick · Accepted Answer · 2014-12-06 16:01:04Z

0

You'll want to use, regexp_substr which returns a substring that matches the regular expression.

REGEX_SUBSTR

answered Dec 6, 2014 at 16:01

Nick

2,51419 silver badges26 bronze badges

Collectives™ on Stack Overflow

Oracle: Extract number from String

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related