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.

CodePudding user response：

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.