I have the following query where I have a table which is storing from information with a delimiter

SQL version - Microsoft SQL Azure (RTM) - 12.0.2000.8

DECLARE @commanTable TABLE
(
  CommaId NVARCHAR(MAX),
  Name NVARCHAR(500)
)

DECLARE @commanTable1 TABLE
(
   CommaId INT,
   Name NVARCHAR(500)
)

INSERT INTO @commanTable VALUES ('2324205.3933251.7336404', 'Test1'), 
('2324206.3933252.7336405', 'Test2')

INSERT INTO @commanTable1 (CommaId, Name)
SELECT  value, Name
FROM   @commanTable CROSS APPLY STRING_SPLIT(CommaId,'.');

SELECT * FROM @commanTable1

Getting the following results

Where I need the results to be

回答

SELECT *, left(CommaId, charindex('.', CommaId) - 1)
FROM   @commanTable

SELECT  *, 
        item1 = left(CommaId, p1.p - 1),
        item2 = substring(CommaId, p1.p + 1, p2.p - p1.p - 1)
FROM    @commanTable t
        cross apply
        (
            select  p = charindex('.', CommaId)
        ) p1
        cross apply
        (
            select  p = charindex('.', CommaId, p1.p + 1)
        ) p2