CODEWITHSUNDEEP
sqlregexpostgresql
wilsonm2
wilsonm2

Reputation: 591

Use Regular Expression to extract text between two characters

I'm using PostgreSQL and I have a list of names in the following format:

"Abbing, Mr. Anthony"
"Abbott, Mr. Rossmore Edward"
"Abbott, Mrs. Stanton (Rosa Hunt)"

And I want to extract the title (i.e. "Mr", "Mrs"). It is always between the comma and the dot.

I'm only new to regular expressions, this is what i'm trying to use but i'm not getting the correct answer.

SELECT SUBSTRING(name from ',..')

I get ", M" as an answer.

I assume this is something very simple to fix.

Thanks

Upvotes: 0

Views: 4905

Answers (3)

FutbolFan
FutbolFan

Reputation: 13713

You could use position and substring function to do this:

SELECT samplename
    ,trim(substring(samplename, position(',' IN samplename) + 1
          , position('.' IN samplename) - position(',' IN samplename))) AS initials
FROM test

SQL Fiddle Demo

Upvotes: 0

davcs86
davcs86

Reputation: 3935

Try

SELECT SUBSTRING(name from ',\s([^\.]+)\.')

Upvotes: 0

user330315
user330315

Reputation: 

substring(name from '(, (Mr)(s){0,1}\.)')

will extract , Mr. or , Mrs.. Note the parentheses around the whole expression. substring( ... from ..) will return the match from the first group in the regex. As I have used (Mr) and (s) to match the titles, I have to put everything between parentheses to make substring() return the whole pattern

to get rid of the leading ', ' you can use trim()

trim(substring(name from '(, (Mr)(s){0,1}\.)'), ', ')

Upvotes: 1

Related Questions