Home How to Find the first occurrence of the ZZ identity and then store the value in variable

Questions

How to Find the first occurrence of the ZZ identity and then store the value in variable

March 16, 2022

Here I want to store the value '202202171100DF' Which is coming after the first occurrence of ZZ.

DECLARE @Element1 VARCHAR(100)='DO#0000000000#ZZ#202202171100DF#ZZ#1#ZZ#20122877GH'

Below query will get the all values after the first occurrence of ZZ. I just want, it should get the first occurrence of ZZ. i.e.'202202171100DF'

SELECT SUBSTRING(@Element1,CHARINDEX('ZZ',@Element1)+3,len(@Element1));

>Solution :

As I mentioned in the comments, this really isn’t a task for T-SQL. It has poor string manipulation functionality. There are a couple of methods you could use, though.

The first is similar to the method you attempted, using SUBSTRING and CHARINDEX:

DECLARE @ELEMENT1 VARCHAR(100)='DO#0000000000#ZZ#202202171100DF#ZZ#1#ZZ#20122877GH';

SELECT SUBSTRING(@ELEMENT1, CHARINDEX('ZZ#', @ELEMENT1) + 3, CHARINDEX('#', @ELEMENT1 + '#',CHARINDEX('ZZ#', @ELEMENT1) + 3) - (CHARINDEX('ZZ#', @ELEMENT1) + 3));

This is pretty messy, with all the references to CHARINDEX, but does get the job done.

Another method is the split the string into parts and then return the first row where the "identifier" has the value 'ZZ'. Unfortunately (hopefully until SQL Server 2022) SQL Server’s in-built string splitter, STRING_SPLIT doesn’t return the ordinal position, so we’ll need to use something else such as a JSON splitter. Then you can use a pivot to match the identifier to the value and then get the "first" entry:

DECLARE @ELEMENT1 VARCHAR(100)='DO#0000000000#ZZ#202202171100DF#ZZ#1#ZZ#20122877GH';

WITH Identities AS(
    SELECT [key] / 2 AS Entry,
           MAX(CASE WHEN [key] % 2 = 0 THEN [Value] END) AS [Identity],
           MAX(CASE WHEN [key] % 2 = 1 THEN [Value] END) AS [Value]
    FROM OPENJSON(CONCAT('["',REPLACE(@ELEMENT1,'#', '","'),'"]')) OJ
    GROUP BY [key] / 2)
SELECT TOP (1)
       [Value]
FROM Identities
WHERE [Identity] = 'ZZ'
ORDER BY Entry ASC;

But, again, ideally do this out of T-SQL. If you need to send such data to SQL Server, do so in a normalised format or in properly defined JSON or XML, which SQL Server can natively consume (in all fully supported versions).