0

I have a column in my table called display name and the data looks like this John Smith I am looking to see if there is away to split the data like so:

display name as first name => John, display name as last name => Smith

Is this possible?

Perception
  • 79,279
  • 19
  • 185
  • 195
user1269625
  • 3,121
  • 26
  • 79
  • 111
  • Which database are you using? – Andrew Logvinov Jan 29 '13 at 17:13
  • PhpMyAdmin on my localhost – user1269625 Jan 29 '13 at 17:16
  • That's not a database actually, I think. Probably you're using MySQL. You should look for some function that splits by regex and use space as regex capturing first group as first name and second one - as last name. – Andrew Logvinov Jan 29 '13 at 17:20
  • possible duplicate of [MySQL split value from one field to two](http://stackoverflow.com/questions/2696884/mysql-split-value-from-one-field-to-two) – Alex K. Jan 29 '13 at 17:21
  • There are lots of edge cases here that you are going to have to consider. Have you considered double barrelled or hyphenated names? What about middle names? If you are going to ignore these then you can simply use the left/right commands up to the charindex of the first occurrence of ' ' i.e. a space – JordanMazurke Jan 29 '13 at 17:22

2 Answers2

2

Assuming MySQL, something like this should work if you always have a single space. 

SELECT Substr(name, 1, Instr(name, ' ')) as FirstName,
   Substr(name, Instr(name, ' ')) as LastName
FROM DisplayName

Here is some sample Fiddle that shows you how it works (and when it doesn't).

Good luck.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
0

There are multiple solutions available for this common issue. If you are just querying the DB, then you can use the example provided by @sgeddes.

Now if you would like to take the values you get from that query and put it, in its own column, you will want to create a new column; 

alter table table_name
add column first_name varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL
after col_name;

⬆️ The after statement help you choose the location of your new column.

Then you can update said column with an update statement;

update table_name
set first_name = SUBSTRING_INDEX(SUBSTRING_INDEX(display_name, ' ', 1), ' ', -1)

This worked for me. Good luck

utf8mb4
  • 13
  • 4