Question 18471 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Removing Parenthesis After A Character – Amazon Web Sevices

I am trying to remove the Parenthesis and its contents after a comma for an Employee Name Field.

I cant seem to get the syntax right to remove special characters and numbers AFTER the first comma. Then i would like to rearrange them if possible to show First Name then Last Name.

Employee Name

Doe (Developer), John(123456)

I can get as far as this:

Doe (Developer), John ()
Doe , John

translate(column_1, 'a0123456789', 'a') as employee_name,
 regexp_replace(employee_name, '\([^)]*\)')

I’d like to be able to get to

Doe (Developer), John

but the perfect scenario would be

John (Developer) Doe
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I think i found an answer. thanks for all the help though! select

    replace(translate(employee_name, 'a0123456789', 'a'), '()', '') as employee_name2, split_part(replace(translate(employee_name, 'a0123456789', 'a'), '()', ''),',',2) ||' '|| split_part(replace(translate(employee_name, 'a0123456789', 'a'), '()', ''),',',1) as employee_name3,

    from mytable


  2. 0

    We can try using REGEXP_REPLACE with capture groups:

    SELECT REGEXP_REPLACE(employee_name, '^(\w+).*?(\(.*?\)),[ ]*(\w+).*$', '$3 $2 $1') AS first_last
FROM yourTable

    Here is a working regex demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search