Amazon Redshift supports various built in functions, you can use them to extract the numbers, alphanumeric or specific patters from strings. In this article, we will discuss about Redshift extract numbers using regular expressions and examples. In data warehouse environment, you may have different types data files extracted from different data sources. Data might be corrupted or may have unwanted characters, you can clean such a data using Redshift regular expressions.
You can read about regular expression in my other post:
Redshift Extract Numbers using Regular Expressions
Below are the some of the examples for Redshift extract number from string values. You may receive the requirement that required extracting part of string from the data available in the table. Here are some of the common uses of Redshift regular expressions with some examples:
Redshift Extract 6 digit’s numbers from string value examples
Here is an example to extract the 6 digit’s ID number from string data using Redshift REGEXP_SUBSTR regular expressions. You can modify regular expression pattern to extract any number of digits based on your requirements.
https://gist.github.com/ea3f31fc9419230d121f1648dc67d49d
Redshift Extract Numbers from the string examples
Here is the example to extract the number from the string using Redshift regexp_replace regular expressions
https://gist.github.com/713fbfb611d566f9e10190291ea446b7
As shown in above example, there are different ways to extract the numbers from the string using Redshift regexp_replace regular expressions. in first example above, you are extracting only digits from the string and in second, you are replacing all occurrence of alphabets, special characters with none (”). If you know the regular expression pattern, you can apply any regular expression function to get desired output.
Redshift Extract Alphanumeric from the string examples
You may have requirement to extract alphanumeric values that is matching particular patters. In this example we will check how to extract alphanumeric values. for example, extract alphanumeric that starts with ‘U’ and followed by 6 digits.
https://gist.github.com/934d1620258c45ddbb55e5e549c980d3
Related Articles