Splitting a string into First Name ,Mid Name and Last Name

Example 1–(sql query for split)

declare @NameString as varchar(2000)
set @NameString =’suvasish r das’
SELECT LEFT(@NameString,CHARINDEX(‘ ‘,@NameString)) AS FName
,CASE
WHEN LEN(@NameString)-(CHARINDEX(‘ ‘,@NameString)+CHARINDEX(‘ ‘,REVERSE(@NameString))) > 0 THEN
SUBSTRING(@NameString,CHARINDEX(‘ ‘,@NameString)+1,LEN(@NameString)-(CHARINDEX(‘ ‘,@NameString)+CHARINDEX(‘ ‘,REVERSE(@NameString))))
ELSE
”
END AS MName
,RIGHT(@NameString,CHARINDEX(‘ ‘,REVERSE(@NameString))-1) AS LName

example 2–Use of table valued function

/***********************************************************************************
Function Description    : spliting string
———————————————————————————
Date Created        : 23-SEPT-2008
Author            :    SUVASISH
———————————————————————————
Tables Referred
Name                Select    Insert    Update    Delete
Location Master        *
———————————————————————————
Change History
Date Modified        :
Changed By        :
Change Description       :

***********************************************************************************/
create  function [dbo].[fn_Split]
(
@i_string VARCHAR(50)
)
RETURNS TABLE
AS

RETURN
(
SELECT LEFT(@i_string,CHARINDEX(‘ ‘,@i_string)) AS FName
,CASE
WHEN LEN(@i_string)-(CHARINDEX(‘ ‘,@i_string)+CHARINDEX(‘ ‘,REVERSE(@i_string))) > 0 THEN
SUBSTRING(@i_string,CHARINDEX(‘ ‘,@i_string)+1,LEN(@i_string)-(CHARINDEX(‘ ‘,@i_string)+CHARINDEX(‘ ‘,REVERSE(@i_string))))
ELSE
”
END AS MName
,RIGHT(@i_string,CHARINDEX(‘ ‘,REVERSE(@i_string))-1) AS LName

)

Note:we can use the above function as

select * from [dbo].fn_Split(‘Suvasish R Das’)