0

I have a database column of name

name
-----
Sachin tendulkar
Rahul dravid

Now i want to split as the name and last_name update all the rows in database which around 500 data's,

name       last_name
----       ----------
Sachin     tendulkar
Rahul      dravid

Is it possible with sql query?

I have tired the below solution

How to split one column into two columns in SQL Server

Here is mysql-query.

select 
    case when CHARINDEX('_',name)>0 
         then SUBSTRING(name,1,CHARINDEX('_',name)-1) 
         else name end firstname, 
    CASE WHEN CHARINDEX('_',name)>0 
         THEN SUBSTRING(name,CHARINDEX('_',name)+1,len(name))  
         ELSE NULL END as lastname
from user_name

I have tried this method i am getting some error, i don't know how to fixed this errorsenter image description here

Here is database structure

enter image description here

Dharman
  • 30,962
  • 25
  • 85
  • 135
jvk
  • 2,133
  • 3
  • 19
  • 28
  • 2
    I'm guessing the error is because you're using an example from SQL Server, but trying to execute it in MySQL. They are not the same product. I've removed the SQL Server tag from your question since you look to be working with MySQL. – squillman Oct 01 '18 at 16:57

1 Answers1

1

Replace charindex() with locate() and len() with length(). Those are the equivalents of the SQL Server functions in MySQL.

(No, I'm not gonna type the query off the screenshot here and insert the changes.)

sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • Hi @sticky bit, Thanks for your time, i ran sql query after as you mention changes, but still getting same error, I also updated with sql query in the question – jvk Oct 01 '18 at 17:11
  • @jvk: Works for me: http://sqlfiddle.com/#!9/6d33a2/5 Only one other thing you want to change is the underscores to spaces as the name parts are separated by space, not underscores in your case. – sticky bit Oct 01 '18 at 17:17