Reputation: 51
Given a string, strip away the numbers that come after letters. If the inputs look like the left column below, I want the outputs to look like the right column.
inputs | outputs |
---|---|
415-424-4005 XT. 21 | 415-424-4005 XT. |
1-800-552-3319 EXT 2606 | 1-800-552-3319 EXT |
713-840-9300 X104 | 713-840-9300 X |
800-454-1628 OPTION#1 | 800-454-1628 OPTION# |
+1 (844) SAVE-529 | +1 (844) SAVE- |
424-252-4813 (EXT 5013) | 424-252-4813 (EXT ) |
1-800-55OAKWELL | 1-800-55OAKWELL |
+1 (212) 603-2800 (EST) | +1 (212) 603-2800 (EST) |
Upvotes: 1
Views: 83
Reputation: 121919
Split the input strings into parts with regexp_match()
(used in a lateral join) and remove digits from the second part with regexp_replace()
:
select input, concat(part[1], regexp_replace(part[2], '\d', '', 'g')) as output
from the_data
cross join regexp_match(input, '(.+[A-Z]+)(.*)') as part
In more complex queries a custom function can be very useful. Define the function once and use it in various contexts:
create function remove_digits_after_letters(input text)
returns text language sql immutable as $$
select concat(part[1], regexp_replace(part[2], '\d', '', 'g'))
from regexp_match(input, '(.+[A-Z]+)(.*)') as part
$$;
select remove_digits_after_letters('415-424-4005 XT. 21')
Test it in Db<>fiddle.
Read about the functions in the documentation.
Upvotes: 1