Extract Just Numbers from a String of Characters

SarahL

Jim Knicely authored this tip.

The easiest way to extract just the numbers from a string of characters is to simply remove any character that isn’t a number!

Example:

dbadmin=> SELECT c1, regexp_replace(c1, '\D', '') FROM bunch_of_characters;
       c1        | regexp_replace
-----------------+----------------
 A1B2C3D4E5      | 12345
 ABCDE12345      | 12345
 Phone: 555-0100 | 5550100
 1!2$3%4^5       | 12345
(4 rows)

Have Fun!