How to split one column into two columns in SQL Server

Question

I have small question about SQL Server, please tell me how to solve this issue

Table: emp

   id    name
  ---------------
   1    abc_rao
   2    nani
   3    hari_babu
   4    kalibabu
   5    ab_tan

Based on that table I want output like below

   id   firstname   lastname
   1      abc       rao
   2      nani      nothing
   3      hari      babu
   4      kalibabu  nothing
   5      ab        tan

and I tried like this:

select 
    SUBSTRING(name, 1, CHARINDEX('_', name) - 1) as firstname , 
    SUBSTRING(name, CHARINDEX('_', name) + 1, LEN(name)) as lastname 
from emp

but I'm not getting exactly the expected result.

Instead, I'm getting an error:

Msg 537, Level 16, State 2, Line 3
Invalid length parameter passed to the LEFT or SUBSTRING function.

Please tell me how to solve this

Issue using query in SQL Server

Answer 1

try this:

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 emp

you can use CASE command to control is last name available.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

declare @t table (id int, name  varchar(50))

insert into @t (id,name) values( 1    ,'abc_rao')
insert into @t (id,name) values( 2    ,'nani')
insert into @t (id,name) values( 3    ,'hari_babu')
insert into @t (id,name) values( 4    ,'kalibabu')
insert into @t (id,name) values( 5    ,'ab_tan')

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 @t

Results:

| FIRSTNAME | LASTNAME |
|-----------|----------|
|       abc |      rao |
|      nani |   (null) |
|      hari |     babu |
|  kalibabu |   (null) |
|        ab |      tan |

UPDATED: sqlfiddle added

Answer 2

declare @table table(name varchar(300))
insert into @table values('Raj Parmar')
insert into @table values('Ronak mer')
insert into @table values('Mayuri nikunj trivedi')

Select 
DISTINCT name,
   SUBSTRING(name, 1, CHARINDEX(' ', name) - 1) as FirstName,
   RTRIM(LTRIM(REPLACE(REPLACE(name,SUBSTRING(name , 1, CHARINDEX(' ', name) - 1),''),REVERSE( LEFT( REVERSE(name), CHARINDEX(' ', REVERSE(name))-1 ) ),'')))as MiddleName,
   REVERSE( LEFT( REVERSE(name), CHARINDEX(' ', REVERSE(name))-1 ) ) as LastName
From @table

name                    FirstName      MiddleName          LastName
Mayuri nikunj trivedi    Mayuri        nikunj               trivedi
Raj Parmar               Raj                                Parmar
Ronak mer               Ronak                               mer