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

Posted April 10, 2018 by Soniya Shah, Information Developer

Modern Database Analytics
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!