sql - SQL Server：获取子字符串（名字，姓氏，姓氏）

Question

我正在寻找一种方法来获取下表：

CREATE TABLE [dbo].[#temp]
(
    [ID_TASK] [NVARCHAR](300) NULL,
    [CHNAME_NAME] [NVARCHAR](300) NULL,
    [CHNAME_PHONE] [NVARCHAR](300) NULL 
) ON [PRIMARY]

INSERT INTO [dbo].[#temp] 
VALUES ('ID005', 'Anderson Abreu Oliveira', '68157120'),
       ('ID006', 'Gonzalez-IV', '64106929'),
       ('ID009', 'Parker W.H.', '60994308')

我正在使用这个 SQL 查询：

SELECT
    ID_TASK, CHNAME_NAME,
    RTRIM(LTRIM(SUBSTRING(CHNAME_NAME, 1, CHARINDEX(' ', CHNAME_NAME)))) AS SURNAME,
    RTRIM(LTRIM(SUBSTRING(CHNAME_NAME, CHARINDEX(' ', CHNAME_NAME) + 1, LEN(CHNAME_NAME) - (CHARINDEX(' ', CHNAME_NAME) - 1)))) AS  FIRSTNAME,
    REPLACE((RTRIM(LTRIM(CHNAME_PHONE))), '8-', '') AS CHNAME_PHONE
FROM 
    [dbo].[#temp]

但我得到了这个结果：

如何修复此 SQL 查询以获得所需的结果？谢谢

标签： sqlsql-serversql-server-2008

Answer 1

这是一个相当先进的事情，我已经做了一些 SQL 来让你到达那里，但没有整理它......

DECLARE @temp TABLE (
    [ID_TASK] [nvarchar](300) NULL,
    [CHNAME_NAME] [nvarchar](300) NULL,
    [CHNAME_PHONE] [nvarchar](300) NULL
)

INSERT INTO @temp
SELECT 'ID005','Anderson Abreu Oliveira','68157120' UNION ALL
SELECT 'ID006','Gonzalez-IV','64106929' UNION ALL
SELECT 'ID009','Parker W.H.','60994308'


select ID_TASK, col1, col2, col3
from
(
  select a.ID_TASK, Item , 'col' + CONVERT(NVARCHAR(10), ItemNumber) ItemNumber
  from @temp a
  CROSS APPLY [dbo].[DelimitedSplit] ( REPLACE(a.CHNAME_NAME, '-', ' '), ' '  ) b
) d
pivot
(
  MAX(item)
  for itemNumber in (col1, col2, col3)
) piv
GROUP BY ID_TASK, col1, col2, col3

辅助功能：

CREATE FUNCTION [dbo].[DelimitedSplit] (
    @pString VARCHAR(MAX),
    @pDelimiter CHAR(1)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
    WITH E1(N) AS ( -- 10
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    ),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b),
    E3(N) AS (SELECT 1 FROM E2 a, E2 b, E2 c),
    E4(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c, E3 d),
    cteTally(N) AS (
        SELECT 0 UNION ALL
        SELECT TOP ( DATALENGTH( ISNULL( @pString, 1 ) ) ) ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) FROM E4
    ),
    cteStart(N1) AS (
        SELECT t.N + 1
        FROM cteTally t
        WHERE ( SUBSTRING( @pString, t.N, 1 ) = @pDelimiter OR t.N = 0 )
    )
-- ------
    SELECT
        ItemNumber = ROW_NUMBER() OVER( ORDER BY s.N1 ),
        Item = SUBSTRING( @pString, s.N1, ISNULL( NULLIF( CHARINDEX( @pDelimiter, @pString, s.N1 ), 0) - s.N1, 1000000 ) )
    FROM cteStart s
GO