Skip to content

Instantly share code, notes, and snippets.

@vithalsamp

vithalsamp/Redshift extract number1.sql

Created February 26, 2018 04:24
Show Gist options
  • Save vithalsamp/713fbfb611d566f9e10190291ea446b7 to your computer and use it in GitHub Desktop.
Save vithalsamp/713fbfb611d566f9e10190291ea446b7 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Redshift extract number1.sql
training=# select regexp_replace(string, '[^[:digit:]]', ' ') from (select ' My zip is 12345 and id is 389362. Send details to my house # 8/22' as string) a;
regexp_replace
--------------------------------------------------------------------
12345 389362 8 22
(1 row)
--Another way to extract the numbers from the string using Redshiftregexp_replace regular expressions
training=# select REGEXP_replace(string, '[a-z/-/A-z/./#/*]', '') from (select ' My zip is 12345 and id is 389362. Send details to my house # 8/22' as string) a;
regexp_replace
-------------------------------
12345 389362 822
(1 row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment