CODEWITHSUNDEEP
postgresql
John Jung
John Jung

Reputation: 51

For string, strip away the numbers that come after letters

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

Answers (1)

klin
klin

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

Related Questions