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

If you can alter the design at this point then I suggest that you do so. Save the names into two fields to begin with. This is a better design. — krowe, Mar 26 '14 at 05:32
You absoolutely get the expected result - you just never really put your mind into what you wrote. See, for nani (id 2) there is no _, so the charindex will return NULL, which is not valid input for substring (so you need a coalesce there to turn that into a number). And then in your putpu the "nothing" comes exactly from where? — TomTom, Mar 26 '14 at 06:17

Hadi Sharifi · Answer 1 · 2014-03-26T08:33:14.183

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

score 4 · Answer 2 · answered Sep 29 '18 at 09:55

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

score 3 · Answer 3 · edited Apr 17 '17 at 14:41

You miss the case where there's no underscore in the string, which causes the error. Instead, try:

select
    case CHARINDEX('_', name) when 0 then name
        else SUBSTRING(name, 1, CHARINDEX('_', name) - 1) end firstName,

    case CHARINDEX('_', name) when 0 then name
        else SUBSTRING(name, CHARINDEX('_', name) + 1, LEN(name)) end lastname 
from emp

I assume in the case of a single word, you want to show them as both the first name and last name. You can change that value to whatever you prefer.

score 2 · Answer 4 · edited Sep 12 '18 at 05:51

2

declare @tbl table(name varchar(20))

insert into @tbl values ('Abdul$Rahim')

insert into @tbl values('Tariq$Jameel')

select

PARSENAME(replace(name,'$','.'),2) as firstname,
PARSENAME(replace(name,'$','.'),1) as lastname

from @tbl

edited Sep 12 '18 at 05:51

Sociopath

13,068
19
47
75

answered Sep 12 '18 at 05:22

Saikh Rakif

135
3

Alberto vitela · Answer 5 · 2022-10-24T12:54:18.093

Is easier using Using Split_string function and a dynamic pivot

declare @TestPorformance as table ( DirtyId int identity ,[Text] varchar(max) )

insert into @TestPorformance([Text])values('Pedro guevera,58,jalisco') insert into @TestPorformance([Text])values('Juan Sandoval,80,colima') insert into @TestPorformance([Text])values('Karla hinojosa,71,jalisco') insert into @TestPorformance([Text])values('Luis gonzalez,15,jalisco')

select [2] AS [Id] ,[1] AS [Name] ,[3] AS [State] from (select TP.DirtyId ,ROW_NUMBER() OVER(ORDER BY TP.DirtyId) - ((TP.DirtyId -1) * 3) AS Row_Csc_Id ,txt.Value from @TestPorformance TP cross apply string_split(tp.[Text],',') AS Txt) X pivot ( min([value]) for Row_Csc_Id in( [1],[2],[3]) )p

How to split one column into two columns in SQL Server

5 Answers5

Linked

Related