Postgres Regex to Return first word before comma and first word after

Question

I am trying to get the first and last name from a column using regex but I'm stumped.

I'm trying:

select
    substring('SMIRTH JR, DAVID ALLEN', '^[^ ,]+') as namemodified

it should return

SMIRTH, DAVID

but it only returns the last name. I can't figure out how to get both.

I also need it to work for:

SMIRTHJR, DAVID ALLEN

since the data isn't very clean. Any ideas?

The Expected output is 'SMIRTH, DAVID' --oops I'll edit my question. — excelVBAmaster.com
– excelVBAmaster.com, Commented Dec 7, 2017 at 20:45
I tested it, but I get 'SMIRTHJR, DAVID ALLEN' not 'SMIRTH, DAVID' — excelVBAmaster.com
– excelVBAmaster.com, Commented Dec 7, 2017 at 20:48
Try select regexp_replace('SMIRTH, DAVID ALLEN', '^([^,\s]+?)([JS]R)?\y[^,]*,\s*(\S+\y).*$', '\3, \1') — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Dec 7, 2017 at 20:57

RomanPerekhrest · Accepted Answer · 2017-12-07 21:30:20Z

1

Use regexp_matches function instead of substring (assuming Postgresql 9.x):

select
    regexp_matches(regexp_replace('SMIRTHJR, DAVID ALLEN','(JR|SR),', ','), 
                   '([^\s,]+).*?(, [^\s,]+) [^\s,]+$') as namemodified

The above will return SMIRTH, DAVID for both input strings SMIRTHJR, DAVID ALLEN or SMIRTH JR, DAVID ALLEN

edited Dec 7, 2017 at 21:30

answered Dec 7, 2017 at 20:38

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Wiktor Stribiżew Over a year ago

^[^\s,]+|[^\s,]+$ only returns SMIRTH.

Wiktor Stribiżew Over a year ago

But for I also need it to work for: SMIRTHJR, DAVID ALLEN since the data isn't very clean. requirement, it would have been even easier.

RomanPerekhrest Over a year ago

@WiktorStribiżew, while since the data isn't very clean - someone can invent additional cases like SMIRTHJR,DAVIDALLEN - what now?

excelVBAmaster.com Over a year ago

The data always has a space after the comma so we just have to worry about the JR/SR and spaces. I just wish there was a solution that worked on 5.1.1 - I'm stuck on that old version. :(

excelVBAmaster.com Over a year ago

Using @WiktorStribiżew 's tool it works, but on my version I get an error saying that it doesn't recognize the function - regexp_matches isn't supported in my version :(

|

Collectives™ on Stack Overflow

Postgres Regex to Return first word before comma and first word after

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related