Redshift SQL - Extract numbers from string

Question

In Amazon Redshift tables, I have a string column from which I need to extract numbers only out. For this currently I use

translate(stringfield, '0123456789'||stringfield, '0123456789')

I was trying out REPLACE function, but its not gonna be elegant.

Any thoughts with converting the string into ASCII first and then doing some operation to extract only number? Or any other alternatives. It is hard here as Redshift do not support functions and is missing lot of traditional functions.

Edit: Trying out the below, but it only returns 051-a92 where as I need 05192 as output. I am thinking of substring etc, but I only have regexp_substr available right now. How do I get rid of any characters in between

select REGEXP_SUBSTR('somestring-051-a92', '[0-9]+..[0-9]+', 1)

Chiropractor

Better to parse it before putting into Redshift - more functionality, better performance, lower cost... – Trogon

@Trogon "more functionality, better performance, lower cost..." Huh? Have you ever looked into the cost of most analytic databases (Teradata, Vertica, et al)? Redshift is cheap and the SSD nodes are fast. – Johan

@JoeHarris, I agree that Redshift is very low cost, but it doesn't mean that you should fill it up with long textual columns. If you can parse them into more analytical form (numbers, categories, dates...), it is more scalable and cost effective. – Trogon

Answer 1

Better method is to remove all the non-numeric values:

select REGEXP_replace('somestring-051-a92', '[^0-9]', '')

Perkin

If you wanted the opposite of this, i.e. remove all numbers from your string, you can use the following: REGEXP_REPLACE(your_column_or_string, '[0-9/-]', '') – Abby

how would you do that if you want a float back? Like 98.76 – Wylen

select REGEXP_replace('somestring-98.76', '[^0-9.]', '') – Perkin

Answer 2

might be late but I was solving the same problem and finally came up with this

select REGEXP_replace('somestring-051-a92', '[a-z/-]', '')

alternatively, you can create a Python UDF now

Symptomatic

REGEXP_replace(value, '[A-Z/-]|[a-z/-]') – Krongold

wouldn't work with special characters – Perkin

Answer 3

Typically your inputs will conform to some sort of pattern that can be used to do the parsing using SUBSTRING() with CHARINDEX() { aka STRPOS(), POSITION() }.

E.g. find the first hyphen and the second hyphen and take the data between them.

If not (and assuming your character range is limited to ASCII) then your best bet would be to nest 26+ REPLACE() functions to remove all of the standard alpha characters (and any punctuation as well).

If you have multibyte characters in your data though then this is a non-starter.

Johan

What if I have multibyte characters in there? – Chiropractor

Ha, it's a non-starter! If you really have multi-byte chars then these probably should have been split upstream somewhere. – Johan

Answer 4

You can specify "any non digit" that includes non-printable, symbols, alpha, etc.

e.g., regexp_replace('brws--A*1','[\D]')

returns "1"

Contralto