CODEWITHSUNDEEP
postgresql
Guilherme do Valle
Guilherme do Valle

Reputation: 508

Most readable way to get only surname of person in PostgreSQL

I just want to get the surname of the people in my database. If I have the name 'John Mckain William', I want 'Mckain William'. The way I found to do this is with this solution:

RIGHT(person.name, length(person.name) - (length(split_part(person.name, ' ', 1)) + 1))

That way, I get the length of the entire string, subtract the length from the first name, get the length of the surname -1 (so as not to have a space as the first letter) and 'cut' the string with 'RIGHT Function'. But it is not very readable and it is ugly. Is there a better and more elegant way to do this?

Upvotes: 0

Views: 105

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

Using regexp_matches:

(regexp_matches(name, '[^\s]+\s(.+)'))[1]

The regex skips a series of non-whitespace characters, then skips whitespaces and then captures whatever remains, i.e. the surname as is. The expression returns the first capture. If name contains no surname then the expression yields null.

Upvotes: 2

user330315
user330315

Reputation:

What about:

array_to_string((regexp_split_to_array(name, '\s+'))[2:], ' ')

This first converts the name into an array with one element for each word. Then it takes everything starting with the second element and converts the array back to a string.

Upvotes: 1

Related Questions