CODEWITHSUNDEEP
arrayssql-servert-sqlsql-server-2014
Tony
Tony

Reputation: 149

SQL - Get specific element from an array

Suppose I have 2 variables that look like an array:

declare @code nvarchar(200) = 
',10501,10203,10491,10490,10091,10253,10008,10020,10570,10499,';
declare @value nvarchar(200) = 
'True~~100000006~Digital~0~0~~1388.76~Completed~True';

I need to find if @code contains 10490 (for example) and if it does, I need to find a corresponding value (by its index) in @value variable which would be Digital since 10490 is the 4th element in @code array and 4th element of @value array is Digital (note that the 2nd element of the @value array is NULL.

Disclaimer: @code array will ALWAYS contain unique values. It's not possible to have more than 1 10490 for example. @code array will always start and end with ','. Number of elements in @code and @value will always be the same if you take 1st and last comma off the @code variable. I cannot use functions or stored procedures, so everything needs to be done as part of 1 query.

Upvotes: 1

Views: 16979

Answers (4)

Alan Burstein
Alan Burstein

Reputation: 7918

This should be quite simple. If performance is important I would suggest splitting the strings using DelimitedSplit8K. Here's a simple, high-performing solution:

DECLARE @searchFor INT = 10490;

SELECT code = s1.item, s2.item
FROM dbo.DelimitedSplit8K(@code,',')  s1
JOIN dbo.DelimitedSplit8K(@value,'~') s2 ON s2.ItemNumber = s1.ItemNumber-1
WHERE s1.Item = @searchFor;

Results:

code       item
---------- ------------
10490      Digital

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

I think you know, that this is a very bad design... If you can change this, you really should. But this can be solved:

declare @code nvarchar(200) = 
',10501,10203,10491,10490,10091,10253,10008,10020,10570,10499,';
declare @value nvarchar(200) = 
'True~~100000006~Digital~0~0~~1388.76~Completed~True';

--The query will cast both strings to a splittable XML
--The query('/x[text()]') will remove empty entries (leading and trailing comma)
--(...assuming there will never be an empty entry in @code)
--Then it will read a derived numbered list from both
--finally it will join both lists on their PartIndex

WITH Casted AS
(
    SELECT CAST('<x>' + REPLACE(@code,',','</x><x>') + '</x>' AS XML).query('/x[text()]') AS CodeXml
          ,CAST('<x>' + REPLACE(@value,'~','</x><x>') + '</x>' AS XML) AS ValueXml
)
,CodeDerived AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS PartIndex
          ,x.value('text()[1]','nvarchar(max)') AS CodePart
    FROM Casted
    CROSS APPLY CodeXml.nodes('/x') A(x)
)
,ValueDerived AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS PartIndex
          ,x.value('text()[1]','nvarchar(max)') AS ValuePart
    FROM Casted
    CROSS APPLY ValueXml.nodes('/x') A(x)
)
SELECT cd.PartIndex
      ,CodePart
      ,ValuePart
FROM CodeDerived cd
INNER JOIN ValueDerived vd ON cd.PartIndex=vd.PartIndex

The result

inx     CodePart    ValuePart
1       10501       True
2       10203       NULL
3       10491       100000006
4       10490       Digital
5       10091       0
6       10253       0
7       10008       NULL
8       10020       1388.76
9       10570       Completed
10      10499       True

Just add a simple WHERE to reduce this to the one value you need.

Disclaimer: it is not guaranteed, that the numbering with ROW_NUMBER and ORDER BY (SELECT NULL) will ever return the correct sequence, but for a better chance you'd need SQL Server 2016+. For more details: read this link and the other contributions there

Upvotes: 1

donPablo
donPablo

Reputation: 1959

You may need to add some code for when @tofind is not found

declare @code nvarchar(200) = 
',10501,10203,10491,10490,10091,10253,10008,10020,10570,10499,';
declare @value nvarchar(200) = 
'True~~100000006~Digital~0~0~~1388.76~Completed~True';

declare @tofind nvarchar(200) = '10490';
--select left(@code,CHARINDEX(@tofind,@code)) 
--select len(left(@code,CHARINDEX(@tofind,@code))) -  LEN( REPLACE(  left(@code,CHARINDEX(@tofind,@code)) , ',', ''))
declare @nth int;
set @nth =  len(left(@code,CHARINDEX(@tofind,@code))) -  LEN( REPLACE(  left(@code,CHARINDEX(@tofind,@code)) , ',', ''))

declare @SplitOn nvarchar = '~';
declare @RowData nvarchar(200) = @value + '~';

declare @Cnt int = 1
    While (Charindex(@SplitOn,@RowData)>0) and @Cnt < @nth 
    Begin
        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End
Select --Data = ltrim(rtrim(@RowData)),
Case when ltrim(rtrim(@RowData)) = '' then null else
    LEFT(ltrim(rtrim(@RowData)) , CHARINDEX('~',ltrim(rtrim(@RowData))) -1)
end as Result

Upvotes: 1

Pm Duda
Pm Duda

Reputation: 740

Here are two possibilities. In your case I would even try to merge it into one WHILE loop.

SQL Server 2016 and above

(compatibility level 130 and up) you can use built in function STRING_SPLIT

DECLARE @code nvarchar(200) = 
',10501,10203,10491,10490,10091,10253,10008,10020,10570,10499,';
DECLARE @value nvarchar(200) = 
'True~~100000006~Digital~0~0~~1388.76~Completed~True';

DECLARE @valuetosearch nvarchar(200) = '10490'

SELECT value FROM 
(
  SELECT value ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS 'idx'
  FROM STRING_SPLIT ( @value , '~' )
) AS x2
WHERE x2.idx =
  (
    SELECT idx-1 FROM
    ( 
      SELECT value ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS 'idx'
      FROM STRING_SPLIT ( @code , ',' ) 
    ) AS x1
  WHERE x1.[value] = @valuetosearch
)

For earlier versions of SQL Server:

DECLARE @code nvarchar(200) = 
',10501,10203,10491,10490,10091,10253,10008,10020,10570,10499,';
DECLARE @value nvarchar(200) = 
'True~~100000006~Digital~0~0~~1388.76~Completed~True';

DECLARE @valuetosearch nvarchar(200) = '10490'

DECLARE @codetbl AS TABLE (idx int IDENTITY(1,1)
  ,code nvarchar(200))
DECLARE @valuetbl AS TABLE (idx int IDENTITY(1,1)
  ,value nvarchar(200))

DECLARE @name nvarchar(200)
DECLARE @pos int

WHILE CHARINDEX(',', @code) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @code)
  SELECT @name = SUBSTRING(@code, 1, @pos-1)

  INSERT INTO @codetbl
  SELECT @name

  SELECT @code = SUBSTRING(@code, @pos+1, LEN(@code)-@pos)
END

INSERT INTO @codetbl
SELECT @code


WHILE CHARINDEX('~', @value) > 0
 BEGIN
  SELECT @pos  = CHARINDEX('~', @value)
  SELECT @name = SUBSTRING(@value, 1, @pos-1)

  INSERT INTO @valuetbl
  SELECT @name

  SELECT @value = SUBSTRING(@value, @pos+1, LEN(@value)-@pos)
END

INSERT INTO @valuetbl
SELECT @value


SELECT value FROM @valuetbl
WHERE idx = (SELECT idx-1 FROM @codetbl WHERE code = @valuetosearch)

Upvotes: 1

Related Questions