One way to remove whitespace exclusively between numbers and non-numbers in Redshift is by using regular expressions and the REGEXP_REPLACE()
function.
For example, let's say we have a column called phone_number
that contains values like 123 456 7890
and 555-555-1212
. We want to remove any whitespace that appears only between numbers and leave any other whitespace intact.
Here's how we can do it:
SELECT REGEXP_REPLACE(phone_number, '([0-9]+)\s+([^\s0-9])', '\1\2') AS cleaned_phone_number
FROM table_name;
Explanation:
REGEXP_REPLACE()
function searches for a pattern in the phone_number
column and replaces it with a specified string. '([0-9]+)\s+([^\s0-9])'
matches any sequence of one or more digits ([0-9]+
) followed by one or more whitespace characters (\s+
) that are immediately followed by a non-digit character ([^\s0-9]
). ( )
capture the matched digits and non-digit characters as two separate groups, which we refer to in the replacement string as \1
and \2
, respectively. '\1\2'
replaces the matched pattern with the two captured groups concatenated without the whitespace in between. Note that this regular expression pattern assumes that there is always at least one non-digit character in the phone number, such as a hyphen, to act as a delimiter. If some phone numbers don't have a non-digit delimiter, the pattern would need to be adjusted accordingly.
Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss
Asked: 2023-07-10 20:37:25 +0000
Seen: 10 times
Last updated: Jul 10 '23
How can arrays with different numbers of rows be stacked on top of each other?
What is the method to calculate the total of two whole numbers in the programming language C?
What is the method for determining if two numbers are relatively prime?
How can binary division be performed for signed numbers using 2's complement?
What is the method to calculate the total of all the numbers in this column?
Using JMESPath, how many occurrences are there in an array?
Can you assist me with correcting errors in a program for adding and subtracting numbers?