CODEWITHSUNDEEP
oracle11g
user1463065
user1463065

Reputation: 595

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

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.

Upvotes: 0

Views: 3353

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

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;

screen capture of demo below

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

Upvotes: 2

Related Questions