get new fields using existing string in sql server

Question

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

Answer 1

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;

Answer 2

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

Answer 3

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     |  
+----+-----------------------+-----------+-------------+----------+  

Answer 4

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.

Answer 5

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:

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.