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) |
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.