Get new fields using existing string-CodePudding

How to split string into multiple columns in SQL Server? Here before 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](500) 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 someone please tell me how to write query to achieve this task in SQL Server.

CodePudding user response：

Please try the following solution.

It is using XML and XQuery. XQuery data model is based on ordered sequences. Exactly what we need for the task.

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><![CDATA['   
        REPLACE([name], @separator, ']]></r><r><![CDATA[')   
        ']]></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     |
 ---- ----------------------- ----------- ------------- ----------

CodePudding user response：

Here is an option using a bit of JSON and conditional aggregation

To be clear... Parsing names is a slippery slope, but if Pos1 is First and PosL is last name and everything else is middle.

Example

Select A.*
      ,B.*
 From  EmpHistory A
 Cross Apply (
                Select FirstName = coalesce(max(case when [key]=1   then value end),'')
                      ,MiddleName= coalesce(string_agg(case when [key]>1 and [key]<cnt then value end,' ') within group (order by [key]),'')
                      ,LastName  = coalesce(max(case when [key]=Cnt and Cnt>1 then value end),'')
                 From (
                       Select [Key] = [key] 1
                             ,Value
                             ,Cnt = sum(1) over()
                        From  OpenJSON( '["' replace(string_escape(Name,'json'),' ','","') '"]' )
                      ) B1
             ) B

Results

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

CodePudding user response：

Here we use a CTE to get the position of the first and last spaces and then use them in the main query.

with cte as
(select 
  name,
  charindex(' ',name) fi,
  len(name) l,
  charindex(' ',reverse(name))la
from EmpHistory
)
select
  *,
  case when fi = 0 then name
    else left(name,fi) end
    as  first_name,
  case when fi   la < l 1 and
    la > 0 then 
    substring (name,fi   1,l - fi - la)
    else '' end as middle_names,
  case when fi = 0 then ''
    else right(name,la) end
    as last_name
from cte
GO

name                  | fi |  l | la | first_name | middle_names | last_name
:-------------------- | -: | -: | -: | :--------- | :----------- | :--------
abc nani ravi jai     |  4 | 17 |  4 | abc        | nani ravi    |  jai     
rani xy               |  5 |  7 |  3 | rani       |              |  xy      
ravi                  |  0 |  4 |  0 | ravi       |              |          
hari go pani          |  5 | 12 |  5 | hari       | go           |  pani    
pani gh hani gov hani |  5 | 21 |  5 | pani       | gh hani gov  |  hani

db<>fiddle here