I'm trying to create bulk import sql script and I have a problem when using ']' with SUBSTRING function. the script doesn't return output as expected

Declare @List nvarchar(MAX) = '[Mohammed-Alkronz:7722],[Alex-John:4233],[Maria-Jose:5567]';
Declare @StartIndex int = CHARINDEX('[',@List)   1;
Declare @LastIndex int = CHARINDEX(']',@List) - 1;
Declare @ListLen int = LEN(@List);
Declare @Delimeter int = CHARINDEX(',',@List);
Declare @record nvarchar(160);
Declare @FirstName nvarchar(50);
Declare @LastName nvarchar(50);
Declare @Extension nvarchar(50);
while(@Delimeter <> 0)
    BEGIN
        set @record = SUBSTRING(@List,@StartIndex,@LastIndex);
        set @FirstName = SUBSTRING(@record,1,CHARINDEX('-',@record)-1);
        set @LastName = SUBSTRING(@record,CHARINDEX('-',@record) 1,CHARINDEX(':',@record)-1);
        print CHARINDEX(':',@record)-1;
        set @Extension = SUBSTRING(@record,CHARINDEX(':',@record) 1,@LastIndex);
        print CHARINDEX(':',@record) 1;
        INSERT INTO Employee VALUES(@FirstName,@LastName,@Extension);

        set @List = SUBSTRING(@List,@Delimeter 1,@ListLen);
        set @StartIndex = CHARINDEX('[',@List)   1;
        set @LastIndex = CHARINDEX(']',@List) - 1;
        set @ListLen = LEN(@List);
        set @Delimeter = CHARINDEX(',',@List);

END

    set @record = SUBSTRING(@List,@StartIndex,@LastIndex);
    set @FirstName = SUBSTRING(@record,1,CHARINDEX('-',@record)-1);
    set @LastName = SUBSTRING(@record,CHARINDEX('-',@record) 1,CHARINDEX(':',@record)-1);
    set @Extension = SUBSTRING(@record,CHARINDEX(':',@record) 1,@LastIndex);
    INSERT INTO Employee VALUES(@FirstName,@LastName,@Extension);

select * from Employee

FirstName  LastName     Extension
---------- -------------- -------------
Mohammed   Alkronz:7722   7722
Alex       John:4233      4233
Maria      Jose:5567      5567

I'm expecting to get result like this:

FirstName  LastName     Extension
---------- -------------- -------------
Mohammed   Alkronz        7722
Alex       John           4233
Maria      Jose           5567

CodePudding user response：

I would suggest switching to a set based method first, a WHILEis a terrible solution here.

Firstly, you can use STRING_SPLIT to split the values. Then you can use TRIM to remove the leading/trailing brackets ([]).

Then you can use a mix of LEFT, SUBSTRING and STUFF, all with CHARINDEX to retain/remove the needed characters:

DECLARE @List nvarchar(MAX) = '[Mohammed-Alkronz:7722],[Alex-John:4233],[Maria-Jose:5567]';

SELECT LEFT(V.Trimmed,CHARINDEX('-',V.Trimmed)-1) AS Forename,
       SUBSTRING(V.Trimmed, CHARINDEX('-',V.Trimmed)   1, CHARINDEX(':',V.Trimmed) - CHARINDEX('-',V.Trimmed) - 1) AS Surname,
       STUFF(V.Trimmed, 1, CHARINDEX(':',V.Trimmed), N'') AS Extension
FROM STRING_SPLIT(@List,',') SS
     CROSS APPLY (VALUES(TRIM('[]' FROM SS.[value])))V(Trimmed);

This does assume all your data is in the format Forename-Surname:Extension. If any part of the format is missing, it will error.

CodePudding user response：

Agree with Larnu, looping and string interpolation are both terrible things to try to do in SQL Server. Since you're on SQL Server 2017, another set-based approach is to manipulate your data just slightly to conform to JSON:

DECLARE @List nvarchar(max) = N'[Mohammed-Alkronz:7722],'
                              N'[Alex-John:4233],[Maria-Jose:5567]';

;WITH src AS 
(
  SELECT v = j1.value, j2.* FROM 
  OPENJSON('['   TRANSLATE(@List, '[]-', '"":')   ']') AS j1
  CROSS APPLY
  OPENJSON('["'   REPLACE(value,':','","')   '"]') AS j2
)
SELECT FirstName = [0], LastName = [1], Extension = [2]
  FROM src 
  PIVOT (MAX(value) FOR [key] IN ([0],[1],[2])) AS p;

Output:

• Example db<>fiddle

And if you want the output to reflect the original order of the strings:

DECLARE @List nvarchar(max) = N'[Mohammed-Alkronz:7722],'
                              N'[Alex-John:4233],[Maria-Jose:5567]';

;WITH src AS 
(
  SELECT v = j1.value, k = j1.[key], j2.* FROM 
-----------------------^^^^^^^^^^^^^
  OPENJSON('['   TRANSLATE(@List, '[]-', '"":')   ']') AS j1
  CROSS APPLY
  OPENJSON('["'   REPLACE(value,':','","')   '"]') AS j2
)
SELECT FirstName = [0], LastName = [1], Extension = [2]
  FROM src 
  PIVOT (MAX(value) FOR [key] IN ([0],[1],[2])) AS p
  ORDER BY k;
--^^^^^^^^^^

• Example db<>fiddle