7

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
  • 605,456
  • 145
  • 1,078
  • 1,228
John
  • 1
  • 13
  • 98
  • 177

3 Answers3

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;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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
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_;
Ben Grimm
  • 4,316
  • 2
  • 15
  • 24
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_;
Toto
  • 89,455
  • 62
  • 89
  • 125