Home Postgres Regex to Return first word before comma and first word after
Reply: 1

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

excelVBAmaster.com
1#
excelVBAmaster.com Published in 2017-12-07 20:31:40Z

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?

RomanPerekhrest
2#
RomanPerekhrest Reply to 2017-12-07 21:30:20Z

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.444595 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO