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

Question

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).

Answer 1

Using regexp_replace is more simple:

# select regexp_replace('test1234test45abc', '[^0-9]+', '', 'g');
 regexp_replace 
----------------
 123445
(1 row)

The ^ means not, so any character that is not in the range 0-9 will be replaced with an empty string, ''.

The 'g' is a flag that means all matches will be replaced, not just the first match.

Answer 2

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'