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-10 03:07:48Z

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, 2022 at 3:07

answered Jan 27, 2015 at 14:19

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Over a year ago

Ah, now I have to benchmark the two answers. Looking in to dba.stackexchange.com/questions/42012/…. The other answer works of course so working on it. :-)

John Over a year ago

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.

Erwin Brandstetter Over a year ago

@John: I have been running many benchmarks over the past years. The outcome is as expected.

John Over a year ago

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

Ben Grimm · Accepted Answer · 2015-01-26 21:42:31Z

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, 2015 at 21:42

Ben Grimm

4,3912 gold badges18 silver badges24 bronze badges

1 Comment

John Over a year ago

Thanks! It does work though Erwin's translate() was faster, up-voted at least.

Toto · Accepted Answer · 2015-01-27 14:41:00Z

3

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, 2015 at 14:41

Toto

91.7k63 gold badges97 silver badges135 bronze badges

Collectives™ on Stack Overflow

PostgreSQL SELECT only alpha characters on a row

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related