Replacing a value in a column- Snowflake (SQL)-CodePudding

I'm extracting a number from a string using the following code:

regexp_substr(data, '\\d \.\\d ') AS Age

Where the value is 0 (within the string), I'm getting a null value. Is there any way to correct this within the wider query, so all the nulls are replaced with 0s?

CodePudding user response：

Note \d .\d means "one ore more digits followed by any character followed by one or more digits. This pattern requires at least 3 characters to match, and the middle one doesn't even have to be a digit.

0 does not match that pattern. To match 0 or 42 or 1000000, i.e. any integer that is merely a string of digits, you only need \d

10.5 does not match this pattern, however, so if you need to also capture decimal values, you will need something more complex that handles digits followed by an optional decimal point and more digits:

\d (?:\.\d )?

This pattern will match 0, 0.0, 42, 98.6, etc.

CodePudding user response：

I could be helpful if you have shared your sample data, but not sure if below helps:

select 
    regexp_substr(column1, '\\d (\.?\\d )?')
from values 
    ('test0me'),
    ('234.234'),
    ('test my age 25.')
;

 ------------------------------------------ 
| REGEXP_SUBSTR(COLUMN1, '\\D (\.?\\D )?') |
|------------------------------------------|
| 0                                        |
| 234.234                                  |
| 25                                       |
 ------------------------------------------

Pretty similar to what @kurt suggested, but without the leading "?:".