CODEWITHSUNDEEP
sqlsql-serversql-server-2017
Moe Kronz
Moe Kronz

Reputation: 31

SQL SUBSTRING function doesn't work properly when using CHARINDEX function to locate ']' character. why?

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

Upvotes: 0

Views: 809

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280590

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:

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

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;
--^^^^^^^^^^

Upvotes: 2

Thom A
Thom A

Reputation: 95989

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.

Upvotes: 2

Related Questions