remove non-numeric characters in a column (character varying), postgresql (9.3.5)

postgresql

Solution

For modifying strings in PostgreSQL take a look at The String functions and operators section of the documentation. Function `substring(string from pattern)` uses POSIX regular expressions for pattern matching and works well for removing different characters from your string. (Note that the `VALUES` clause inside the parentheses is just to provide the example material and you can replace it any `SELECT` statement or table that provides the data):

SELECT substring(column1 from '(([0-9]+.*)*[0-9]+)'), column1 FROM
    (VALUES
        ('ggg'),
        ('3,0 kg'),
        ('15 kg.'),
        ('2x3,25'),
        ('96+109')
    ) strings

The regular expression explained in parts:

- `[0-9]+` - string has at least one number, example: `'789'`

- `[0-9]+.*` - string has at least one number followed by something, example: `'12smth'`

- `([0-9]+.\*)*` - the string similar to the previous line zero or more times, example: `'12smth22smth'`

- `(([0-9]+.\*)*[0-9]+)` - the string from the previous line zero or more times and at least one number at the end, example: `'12smth22smth345'`

Problem

I need to remove non-numeric characters in a column (character varying) and keep numeric values in postgresql 9.3.5. Examples: ``` 1) "ggg" => "" 2) "3,0 kg" => "3,0" 3) "15 kg." => "15" 4) ... ``` There are a few problems, some values are like: ``` 1) "2x3,25" 2) "96+109" 3) ... ``` These need to remain as is (i.e when containing non-numeric characters between numeric characters - do nothing).

Original source