get new fields using existing string in sql server

harinathu 6 Reputation points

hi I have one doubt in sql server
how to split string into multiple columns in sql server
here befor first space value consider as firstname and last space after values consider as lastname
and between first and lastspace values consider as middle name .

CREATE TABLE [dbo].[EmpHistory](
[id] [int] NULL,
[name] varchar NULL
INSERT [dbo].[EmpHistory] ([id], [name]) VALUES (1, N'abc nani ravi jai')
INSERT [dbo].[EmpHistory] ([id], [name]) VALUES (2, N'rani xy')
INSERT [dbo].[EmpHistory] ([id], [name]) VALUES (3, N'ravi')
INSERT [dbo].[EmpHistory] ([id], [name]) VALUES (4, N'hari go pani')
INSERT [dbo].[EmpHistory] ([id], [name]) VALUES (5, N'pani gh hani gov hani')

based on above data I want out put like below

id | Firstname | middlename | Last name
1 | abc | nani ravi |jai
2 |rani | |xy
3 |ravi | |
4 |hari |go |pani
5 |pani |gh hani gov |hani

I have tried like below

select substring(name ,charindex(name,''),len(name))firstname,
substring(reverse(name) ,charindex(reverse(name),''),len(name))lasttname
from EmpHistory

above query getting error.

can you please tell me how to write query to achive this task in sql server

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points

    In the following code, the CTE lists the positions of the first (pos1) and last (pos2) space in the column [name]. If pos1 is 0, pos2 should be 0 too. That means no space in the name. If pos1 is greater than 0 and is equal to pos2, that means only one space in the name.

    ;WITH CTE_Space_Positions AS (
        SELECT [id], [name], CHARINDEX(' ', [name]) AS pos1, CASE WHEN CHARINDEX(' ', [name]) > 0 THEN LEN([name]) - CHARINDEX(' ', REVERSE([name])) + 1 ELSE 0 END AS pos2
        FROM [dbo].[EmpHistory]
    SELECT [id], [name],
           CASE WHEN pos1 = 0 THEN [name] ELSE LEFT([name], pos1 - 1) END AS firstname,
           CASE WHEN pos2 > pos1 THEN SUBSTRING([name], pos1 + 1, pos2 - pos1 - 1) ELSE '' END  AS middlename,
           CASE WHEN pos1 = 0 THEN '' ELSE SUBSTRING([name], pos2 + 1, LEN([name]) - pos2) END AS lastname
    FROM CTE_Space_Positions;
    0 comments No comments

  2. Naomi 7,361 Reputation points


    ;WITH cte AS (SELECT id, [name], RTRIM(LEFT(name, ISNULL(NULLIF(CHARINDEX(' ', [name]),0), LEN(name)))) AS FirstName,
    FROM @EmpHistory e
    CROSS APPLY (SELECT REVERSE(name) AS ReversedName) X
    CROSS APPLY (SELECT CHARINDEX(' ', X.ReversedName) AS LastSpacePos) Y
    CROSS APPLY (SELECT CASE WHEN LastSpacePos > 0 THEN REVERSE (LEFT(X.ReversedName, LastSpacePos -1)) END AS LastName) Z)
    SELECT *, CASE WHEN LEN(FirstName) > 0 AND LEN(LastName) > 0 THEN
    NULLIF(SUBSTRING(Name, 1 + LEN(FirstName), LEN(NAME) - LEN(FirstName) - LEN(LastName)),'') END AS MiddleName
    FROM cte
    0 comments No comments

  3. Yitzhak Khabinsky 25,201 Reputation points

    Hi @harinathu ,

    I already answered the same question on the

    Here it is again.


    -- DDL and sample data population, start  
    INSERT @tbl (name) VALUES   
    (N'abc nani ravi jai'),  
    (N'rani xy'),  
    (N'hari go pani'),  
    (N'pani gh hani gov hani');  
    -- DDL and sample data population, end  
    DECLARE @separator CHAR(1) = SPACE(1);  
    SELECT t.*  
     , Firstname = c.value('(/root/r[1]/text())[1]', 'NVARCHAR(100)')  
     , Middlename = c.query('data(/root/r[position() gt 1 and position() lt last()])')  
     .value('.', 'NVARCHAR(500)')  
     , Lastname = IIF(cnt > 1, c.value('(/root/r[last()]/text())[1]', 'NVARCHAR(100)'),'')  
    FROM @tbl AS t  
     CROSS APPLY (SELECT TRY_CAST('<root><r>' +   
     REPLACE([name], @separator, '</r><r>') +   
     '</r></root>' AS XML)) AS t1(c)  
     CROSS APPLY (SELECT c.value('count(/root/r)', 'INT')) AS t2(cnt);  


    | id |         name          | Firstname | Middlename  | Lastname |  
    |  1 | abc nani ravi jai     | abc       | nani ravi   | jai      |  
    |  2 | rani xy               | rani      |             | xy       |  
    |  3 | ravi                  | ravi      |             |          |  
    |  4 | hari go pani          | hari      | go          | pani     |  
    |  5 | pani gh hani gov hani | pani      | gh hani gov | hani     |  
    0 comments No comments

  4. Erland Sommarskog 102.2K Reputation points MVP

    You can also read this short story on my web site: to learn how to do this.

    0 comments No comments

  5. LiHong-MSFT 10,046 Reputation points

    Hi @harinathu
    Please also check this :

    ;WITH CTE AS  
     SELECT *,  
            CASE WHEN CHARINDEX(' ',name)>0   
    		     THEN SUBSTRING(name,1,CHARINDEX(' ',name)-1)   
    			 ELSE name END AS FirstName,  
            CASE WHEN CHARINDEX(' ',name)>0   
    			 ELSE '' END AS LastName  
     FROM #EmpHistory   
    SELECT id,FirstName,  
           CASE WHEN CHARINDEX(' ',name)>0   
                THEN SUBSTRING(name,CHARINDEX(' ',name),LEN(name)-LEN(FirstName)-LEN(LastName))  
    			ELSE '' END AS Middlename,  
    FROM CTE   


    Best regards,

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments