如何在 SQL Server 中將字串拆分為多列？這里在第一個空格值之前被認為是名字，在值之后的最后一個空格被認為是姓氏，在第一個和最后一個空格值之間被認為是中間名。
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')
基于上述資料，我想如下所示
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
我試過如下
select substring(name ,charindex(name,''),len(name))firstname,
substring(reverse(name) ,charindex(reverse(name),''),len(name))lasttname
from EmpHistory
上面的查詢出錯。
有人可以告訴我如何撰寫查詢以在 SQL Server 中完成此任務。
uj5u.com熱心網友回復：
請嘗試以下解決方案。
它使用 XML 和 XQuery。XQuery 資料模型基于有序序列。正是我們需要的任務。
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);
輸出
---- ----------------------- ----------- ------------- ----------
| 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 |
---- ----------------------- ----------- ------------- ----------
uj5u.com熱心網友回復：
這是一個使用一點 JSON 和條件聚合的選項
要清楚......決議名稱是一個滑坡，但如果 Pos1 是 First 并且 PosL 是姓氏并且其他一切都是中間的。
例子
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
結果
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
uj5u.com熱心網友回復：
這里我們使用 CTE 來獲取第一個和最后一個空格的位置，然后在主查詢中使用它們。
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姓名 | 網路 | l | 拉 | 名字 | 中間名 | 姓 :-------------------- | -: | -: | -: | :--------- | :----------- | :-------- abc 納尼拉維杰 | 4 | 17 | 4 | 美國廣播公司 | 納尼拉維 | 齋 拉尼xy | 5 | 7 | 3 | 拉尼 | | xy 拉維 | 0 | 4 | 0 | 拉維 | | 哈里去帕尼 | 5 | 12 | 5 | 哈里 | 走 | 帕尼 pani gh hani gov 哈尼 | 5 | 21 | 5 | 帕尼 | 哈尼政府 | 哈尼
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/448962.html
標籤：sql sql服务器 sql-server-2012 sql-server-2016