get new fields using existing string in sql server

harinathu 6 Reputation points
2022-03-24T18:53:04.93+00:00

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
Transact-SQL
Transact-SQL
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
    2022-03-24T20:04:40.837+00:00

    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
    2022-03-24T20:06:40.32+00:00

    Try:

    ;WITH cte AS (SELECT id, [name], RTRIM(LEFT(name, ISNULL(NULLIF(CHARINDEX(' ', [name]),0), LEN(name)))) AS FirstName,
    Z.LastName
    
    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
    2022-03-24T20:36:51.513+00:00

    Hi @harinathu ,

    I already answered the same question on the stackoverflow.com
    https://stackoverflow.com/questions/71607939/get-new-fields-using-existing-string/71608218#71608218

    Here it is again.

    SQL

    -- DDL and sample data population, start  
    DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, [name] NVARCHAR(500) NULL);   
    INSERT @tbl (name) VALUES   
    (N'abc nani ravi jai'),  
    (N'rani xy'),  
    (N'ravi'),  
    (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);  
    

    Output

    +----+-----------------------+-----------+-------------+----------+  
    | 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
    2022-03-24T22:35:47.893+00:00

    You can also read this short story on my web site: https://www.sommarskog.se/arrays-in-sql.html to learn how to do this.

    0 comments No comments

  5. LiHong-MSFT 10,046 Reputation points
    2022-03-25T03:26:05.773+00:00

    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   
    		     THEN REVERSE(SUBSTRING(REVERSE(name),1,CHARINDEX(' ',REVERSE(name))-1))   
    			 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,  
    	   LastName   
    FROM CTE   
    

    Output:
    186726-image.png

    Best regards,
    LiHong


    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