Vertica Quick Tip: Extract Just Numbers from a String of Characters

Posted April 10, 2018 by Soniya Shah, Information Developer

This blog post was authored by Jim Knicely. 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!