PostgreSQL SELECT only alpha characters on a row

Question

I need to SELECT only alpha characters FROM a row though I'm having trouble with the expression. I've tried:

SELECT id, regexp_replace(_column_name_, '0123456789', '') AS _column_alias_
FROM _table_name_;

I know the general gist would only replace numbers however the column only contains alphanumeric characters to begin with.

So if _column_name_ contains a value a1b2c3 how do I make PostgreSQL return the string abc?

Answer 1

The fastest expression to eliminate all digits from a string is with a plain translate():

SELECT translate(col,'0123456789','') AS col_without_digits
FROM   tbl;

Regular expressions are powerful and versatile but more expensive.

Your mistake was the missing "global" switch as 4th parameter, as pointed out by @Ben. While using regular expressions, you can also use the class shorthand \d:

SELECT regexp_replace(col, '\d', '', 'g') AS col_without_digits
FROM   tbl;

Answer 2

The syntax is regexp_replace(string text, pattern text, replacement text [, flags text]), using 'g' for flags makes the replacement global:

SELECT id, regexp_replace(_column_name_,'[0-9]','','g') AS _column_alias_
FROM _table_name_;