CODEWITHSUNDEEP
sqlsplit
user1269625
user1269625

Reputation: 3209

SQL split column data

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?

Upvotes: 0

Views: 1664

Answers (2)

utf8mb4
utf8mb4

Reputation: 13

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 🍀

Upvotes: 0

sgeddes
sgeddes

Reputation: 62861

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.

Upvotes: 2

Related Questions