How to fetch only decimal number from a string using regex in oracle

Question

I have a string which contains alpha numeric characters. If the string had a decimal value then only i want to fetch decimal value from a string otherwise return blank.

I tried regular expression which is giving both numbers and decimals REGEXP_SUBSTR(string_value, '[0-9][.0-9]+')

Please find below sample data.

Example 1: My string is "182 test 123423". In this there is no decimal value then i need to return blank

Example 2: My string is "Test01.1%Ter 230334" and output should be 01.1

Example 3: My string is "14.05 get fvfdf 2434 Rf" and output should be 14.05

Please suggest me where i am doing wrong.

Tim Biegeleisen · Accepted Answer · 2019-12-30 09:50:55Z

2

Your current attempt is almost correct, but the regex pattern is slightly off. Try this version:

SELECT
    REGEXP_SUBSTR(string_value, '[0-9]+\.[0-9]+')
FROM yourTable;

Demo

Here is an explanation of the regex pattern:

[0-9]+   match one or more digits
\.       followed by a literal decimal point (which should be escaped)
[0-9]+   followed by one or more digits

answered Dec 30, 2019 at 9:50

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

How to fetch only decimal number from a string using regex in oracle

1 Answer 1

Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related