When you work on the different data sources and trying to load data to Redshift from multiple sources, you might encounter type cast issues such as characters in integer column and that leads the loading to fail. Hence, it is very important to perform integer value check when you are not sure about source data types. In this article, we will check how to performance integer value check in Redshift database.
Why you should check for Integer values in Redshift?
When you are working on the data warehouse and heterogeneous data sources, you may receive bad records as a result of transportation issue or file corruption issue. There are many situations you may end up getting character to integer conversion error when you try to load an alphanumeric value to integer type column. To avoid such a situation you really need to check the data or remove the bad records so that loading will continue without any issues.
Advantages of checking integer values.
- Loading will not fail due data conversion error.
- Validate data for any errors in your source file.
- To test if a variable is a number or a numeric string, etc.
Redshift Integer Value Check Methods
There are multiple methods available to check for integer values in Redshift data warehouse system:
One of the easiest and best methods is actually to use the Redshift built-in functions such as a regular expression to remove the bad records and load only numeric or integer data.
Redshift Integer Value Check Examples
You can use REGEXP_SUBSTR regular expression to extract integer values from the given string values.
select REGEXP_SUBSTR(string, '(^|[^[:word:]]|[[:space:]])\\d{6}([^[:word:]]|[[:space:]]|$)') from (select 'My zip is 12345 and id is 389362 . Send details to my house # 8/22' as string) a ;
regexp_substr
---------------
389362
(1 row)The above query will return integer value up-to 18 digits.
This will only return an integer of up to 18 digits. You can also use the above login in the WHERE clause of the source table to filter bad records.
Create User Defined Functions to Check Numeric Value
Another easy method is to write user-defined function that verifies the numeric values.
For more information, follow – Amazon Redshift isnumeric Alternative and Examples
Related Articles,
Hope this helps 🙂