sql server如何分隔全名

Question

我不善于问问题

我想把全名(A，B，C，B，E，F)在许多第一栏中分开--我们用这个代码来做这件事

    SELECT SUBSTRING(Name_Arabic, 1, CASE WHEN CHARINDEX(' ', Name_Arabic) = 0 THEN len(Name_Arabic) ELSE CHARINDEX(' ', Name_Arabic) END) name1,REPLACE(SUBSTRING(Name_Arabic, CASE WHEN CHARINDEX(' ',Name_Arabic) = 0 THEN len(Name_Arabic) ELSE CHARINDEX(' ', Name_Arabic) END + 1, LEN(Name_Arabic)), REVERSE(SUBSTRING(REVERSE(Name_Arabic), 1, CHARINDEX(' ', REVERSE(Name_Arabic)))), '') name2, 
                      REVERSE(SUBSTRING(REVERSE(Name_Arabic), 1, CHARINDEX(' ', REVERSE(Name_Arabic)))) name3
FROM            tabl DROP TABLE tabl

它给了我( name1 =A，name2 = BCD，name3 =F)

我要找的是(name1 =A，name2 =B，name3 =c，name4 =D，name5 =F)

我该怎么做？

Answer 1

拆分名称fyd，然后将值放在单独的列中。

DECLARE @Names TABLE (
    Id          INT,
    NameStr     VARCHAR(100)
)

INSERT @Names (Id, NameStr)
VALUES
(1, 'Name1 Name2 Name3 Name4 Name5 Name6 Name7'),
(2, 'Name1 Name2 Name3'),
(3, 'Name1 Name2 Name3 Name4 Name5')

;WITH Names
AS
(
SELECT n.Id AS NamesId,
    x.Id AS NameId,
    x.[Name]
FROM ( 
    SELECT Id,
    CAST('<X>' + REPLACE(RTRIM(NameStr), ' ', '</X><X>') + '</X>' AS XML) AS NameXML
    FROM @Names 
    ) n
    CROSS APPLY (
        SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Id,
            xmlData.v.value('.', 'VARCHAR(32)') AS [Name]
        FROM n.NameXML.nodes('X') AS xmlData(v)
    )x
)
SELECT Id,
NameStr,
ISNULL(n1.[Name], '') AS Name1,
ISNULL(n2.[Name], '') AS Name2,
ISNULL(n3.[Name], '') AS Name3,
ISNULL(n4.[Name], '') AS Name4,
ISNULL(n5.[Name], '') AS Name5,
ISNULL(n6.[Name], '') AS Name6,
ISNULL(n7.[Name], '') AS Name7
FROM @Names n
    OUTER APPLY (
        SELECT [Name]
        FROM Names
        WHERE NamesId = n.Id
            AND NameId = 1
    ) n1
    OUTER APPLY (
        SELECT [Name]
        FROM Names
        WHERE NamesId = n.Id
            AND NameId = 2
    ) n2
    OUTER APPLY (
        SELECT [Name]
        FROM Names
        WHERE NamesId = n.Id
            AND NameId = 3
    ) n3
    OUTER APPLY (
        SELECT [Name]
        FROM Names
        WHERE NamesId = n.Id
            AND NameId = 4
    ) n4
    OUTER APPLY (
        SELECT [Name]
        FROM Names
        WHERE NamesId = n.Id
            AND NameId = 5
    ) n5
    OUTER APPLY (
        SELECT [Name]
        FROM Names
        WHERE NamesId = n.Id
            AND NameId = 6
    ) n6
    OUTER APPLY (
        SELECT [Name]
        FROM Names
        WHERE NamesId = n.Id
            AND NameId = 7
    ) n7

结果：

​

​

Answer 2

你在找

CREATE TABLE T(
  ID INT IDENTITY(1, 1),
  Val VARCHAR(45)
);

INSERT INTO T(Val) VALUES
('ahmed ali mohamed khalid'),
('ahmed ali mohamed khalid Mahmoud');

WITH C AS
(
SELECT *,
       ROW_NUMBER() OVER(PARTITION BY Val ORDER BY ID) RN
FROM T CROSS APPLY
       (
         SELECT Value
         FROM STRING_SPLIT(Val, ' ')
         WHERE ID = T.ID
       ) TT
)
SELECT DISTINCT
       ID,
       STUFF(
              (
                SELECT ',Name' + CAST(RN AS VARCHAR) + '=' + Value
                FROM C T1
                WHERE T1.ID = T2.ID
                FOR XML PATH('')
              ), 1, 1, ''
            ) Results
FROM C T2;

返回：

+----+----------------------------------------------------------------+
| ID |                            Results                             |
+----+----------------------------------------------------------------+
|  1 | Name1=ahmed,Name2=ali,Name3=mohamed,Name4=khalid               |
|  2 | Name1=ahmed,Name2=ali,Name3=mohamed,Name4=khalid,Name5=Mahmoud |
+----+----------------------------------------------------------------+

演示