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?

Erwin Brandstetter · Accepted Answer · 2022-02-10T03:07:48.303

8

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;

edited Feb 10 '22 at 03:07

answered Jan 27 '15 at 14:19

Erwin Brandstetter

539,169
125
977
1,137

Ah, now I have to benchmark the two answers. Looking in to http://dba.stackexchange.com/questions/42012/how-can-i-benchmark-a-postgresql-query. The other answer works of course so working on it. :-) – John Jan 27 '15 at 14:26
6.076 ms versus 1.773 ms, it definitely was faster and I wonder how much more so when I get to switch my laptop to an SSD. I'll up-vote Ben and accept yours. – John Jan 27 '15 at 14:31
@John: I have been running many benchmarks over the past years. The outcome is as expected. – Erwin Brandstetter Jan 27 '15 at 14:33
Not that I doubted you though I haven't benchmarked anything in PostgreSQL until today and now I've got two more updates for my MySQL/PostgreSQL cross-reference. :-) – John Jan 27 '15 at 14:56

score 4 · Answer 2 · answered Jan 26 '15 at 21:42

4

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_;

answered Jan 26 '15 at 21:42

Ben Grimm

4,226
2
14
24

Thanks! It does work though Erwin's `translate()` was faster, up-voted at least. – John Jan 27 '15 at 14:31

score 2 · Answer 3 · answered Jan 27 '15 at 14:41

2

If you want to remove also punctuation and everything that is not an alpha:

SELECT id, regexp_replace(_column_name_, '[^a-zA-Z]', '', 'g') AS _column_alias_
FROM _table_name_;

answered Jan 27 '15 at 14:41

Toto

86,179
61
85
118

PostgreSQL SELECT only alpha characters on a row

3 Answers3

Linked