7

I have a list of names and I want to separate the first and last words in a person's name.

I was trying to use the "trim" function without success.

Can someone explain how could I do it?

table: 

Names 
Mary Johnson Angel Smith
Dinah Robertson Donald
Paul Blank Power Silver

Then I want to have as a result: 

Names
Mary Smith
Dinah Donald
Paul Silver

Thanks,

Samuel Donadelli
  • 347
  • 1
  • 3
  • 12
  • 1
    It's basically an unsolvable problem. http://stackoverflow.com/questions/1122328/first-name-middle-name-last-name-why-not-full-name/1122371#1122371 `trim()` really doesn't make sense for this problem. Perhaps you could show us what you tried - show us code! - and we can help you make something that works _for this limited set of inputs._ – Matt Ball Mar 12 '14 at 00:25
  • are you wanting to do this with your query? It would be pretty easy to do in your server side scripting language (php perhaps?)...look into `explode()` – A.O. Mar 12 '14 at 00:28
  • A.O. I tried to do it in postgres but no success. I mean I tried to use trim, but it did not work at all. Is there any other function you might know in postgres, something like : First( "my string") and last("my string")? Thanks, – Samuel Donadelli Mar 12 '14 at 00:41
  • 3
    How do you intend to handle a name like `João da Nova`? The last name is `da Nova`. Or many other cases where last names contain spaces. Seriously, don't attempt this, it's *wrong*. http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ . The only way to separate first and last names is to ask the person whose name it is. And even then, you need to be prepared to have them answer "Well, I don't have a last name" or "Well, I don't have a first name". Or "Which last name, I have three different ones". Or "Pinyin or Simplified Chinese characters?" – Craig Ringer Mar 12 '14 at 00:45
  • @Craig: let alone cultures that put the surname before the given name or surnames like "Van Den Heuvel". Names are crazy complicated. – mu is too short Mar 12 '14 at 00:51
  • Ok people, I tried something that worked for the first name: select substring(name from '^.*?(?=[ ])') as name from table . This solved the problem with the first name. Can somebody tell me how can I get the last name using substring... something like Joao da Silva, so after the last space return me "Silva". This is what I need. I am not trying to open a discussion about if a name should be like this or that way. In my specific case I just need to have this information as described here. Thanks for helping me, Samuel – Samuel Donadelli Mar 12 '14 at 00:54
  • @SamuelDonadelli So you don't actually want the "last name", you want "the last word in a string". – Craig Ringer Mar 12 '14 at 00:57
  • @CraigRinger thanks for the hands up. I just edited the question with your clarification. – Samuel Donadelli Mar 12 '14 at 01:22

3 Answers3

7

You can do it simply with regular expressions, like:

substring(trim(name) FROM '^([^ ]+)') || ' ' || substring(trim(name) FROM '([^ ]+)$')

Of course it would only work you are 100% there is always supplied at least a first and a last name. I'm not 100% sure it is the case for everybody in the World. For instance: would that work for names in Chinese? I'm not sure and I avoid doing any assumption about people names. The best is to simply ask the user two fields, one for the "name" and another for "How would you like to be called?".

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
MatheusOl
  • 10,870
  • 3
  • 30
  • 28
  • Shiftplanning.com did this right recently. They guesstimated people's names algorithmically, but asked them to confirm / correct. – Craig Ringer Mar 12 '14 at 01:25
7

Another approach, which takes advantage of Postgres string processing built-in functions:

SELECT split_part(name, ' ', 1) as first_token,
split_part(name, ' ', array_length(regexp_split_to_array(name, ' '), 1)) as last_token
FROM mytable
Ramon
  • 1,415
  • 9
  • 18
emily
  • 136
  • 1
  • 2
  • 1
    `string_to_array()` would be enough, no regex needed (the `regexp_split_to_array` is a lot slower then `string_to_array()`) –  Jan 12 '17 at 16:29
1

Here's how I extracted full names from emails with a dot in them, eg Jeremy.Thompson@abc.com

SELECT split_part(email, '.', 1) || ' ' || replace(split_part(email, '.',  2), '@abc','')
FROM people

Result:

Jeremy    |   Thompson

You can easily replace the dot with a space:

SELECT split_part(email, ' ', 1) || ' ' || replace(split_part(email, ' ',  2), '@abc','')
FROM people
Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321