1

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.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
Tony
  • 149
  • 2
  • 4
  • 14

4 Answers4

1

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)
Pm Duda
  • 741
  • 5
  • 16
  • This will work. Op will just have to make an UDF for string_split.. and then use that instead of the built in string_split in 2016. – Harry Aug 13 '18 at 21:05
  • OP mentioned, that he cannot use functions or stored procedures. – Pm Duda Aug 13 '18 at 21:06
  • sorry.. missed that bit! – Harry Aug 13 '18 at 21:07
  • Attention: `STRING_SPLIT()` does not guarantee to return the correct order. If you need a *position-safe* split approach in v2016+ better use `OPENJSON` or one of the various functions around [You might read this thread](https://stackoverflow.com/a/51401270/5089204) – Shnugo Aug 14 '18 at 08:11
  • @Pm Duda...your 2nd approach worked. I just need to see which once from the 3 proposed working versions is more efficient since it'll have to go through a lot of records. Thank you! – Tony Aug 14 '18 at 20:20
  • Happy to help - just remember - if you are after efficiency, you should combine this two loops into one. – Pm Duda Aug 14 '18 at 20:29
1

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
donPablo
  • 1,937
  • 1
  • 13
  • 18
  • Thank you donPablo! It works well. Like I said earlier, I'll need to test which approach works in the most efficient way. – Tony Aug 14 '18 at 20:21
1

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

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Hi Shungo, I wouldn't call it a bad design for what it was designed. It was never designed to parsed in SQL. It was designed to be parsed by C# which is quite easy to do. This is an audit table as you may have guessed and usually this is what audit tables look like. With that said, your solution works and I really appreciate your help! Apologies for not giving the full picture, but this needs to be done in a SELECT statement for LEFT JOIN. Something like this: left join audit on audit.auditid = (**I need to find that specific record using TOP 1 and ORDER BY here**). How can I do that? – Tony Aug 14 '18 at 16:41
  • @Tony, just wrap my final `SELECT` in one more `cte` and use this as you'd use any other table... – Shnugo Aug 14 '18 at 17:05
  • Hi @Shnugo, I'm not a very strong SQL developer and have never worked with CTE's, so all this is new to me. I tried, but to no avail. Can you give me some pointers here? Here's my pseudo-query: select t1.field1, t1.field2, aud1.CreatedOn, aud1.ChangeData, aud1.AttributeMask from MyTable t1 join audit aud1 on aud1.AuditId = (select top 1 a1.auditid from audit a1 where a1.objecttypecode = 3 and a1.createdOn < t1.DateVar and a1.objectid = t1.ObjectId and a1.AttributeMask like '%10192%' and a1.operation in (1,2) order by a1.CreatedOn desc) where convert(date, t1.dateVar) = '7-27-2018' – Tony Aug 14 '18 at 21:12
  • I also may need to do lot more of these joins to audit table to collect values from different fields. AttributeMask is @Code in my original question. So I need to get Value from Audit (alias = a) table which is stored in ChangedData field. – Tony Aug 14 '18 at 21:18
  • @Tony, uhm... I cannot know what you really need... A `cte` is nothing else than an *ad-hoc* `VIEW`. You can use it like any other table, view or (i)TVF. Semantically its the same as `...FROM (SELECT Something FROM Somewhere) AS SomeSubSelect`. The advantage is the readability. If you need some source in several places, you can write one CTE and use this. Otherwise you'd have to put the same sub-select into your query repeatedly. But the optimizer will decide how to put the query plan. Why can't you use a function? Could you use a `VIEW`? – Shnugo Aug 16 '18 at 12:38
  • Hi @Shnugo, I cannot create a view or function as this is 3rd party application and we'll have to always re-create all custom objects when we get an upgrade. Basically, i'm looking to do something similar to the below. I will need to do probably about 10 joins to audit table to query for changes to different fields. I just need to decode ChangedData based on position of an attribute in the AttributeMask field just like you decoded it in your solution. The way this query is now is it returns string like 'True~~100000006~Digital~0~0~~1388.76~Completed~True' for ChangedData – Tony Aug 16 '18 at 15:50
  • select a1.ChangeData, a1.AttributeMask, a2.ChangeData, a2.AttributeMask from Table1 t join audit a1 on a1.AuditId = (select top 1 a.auditid from audit a where a.objecttypecode = 3 and a.objectid = T.ObjectId and a.AttributeMask like '%10192%' order by a1.CreatedOn desc) join audit a2 on a2.AuditId = (select top 1 a.auditid from audit a where a.objecttypecode = 3 and a.objectid = T.ObjectId and a.AttributeMask like '%10193%' order by a1.CreatedOn desc) where convert(date, t.SomeDate) = '7-27-2018' – Tony Aug 16 '18 at 15:50
  • @Tony This evolves to be a *chameleon question*... Please avoid *follow-up* questions, especially if they add completely new issues. One principle here is *one issue, one question*. This makes it easy to use SO as recherche database. Nobody wants to read long chains of comments... What you should do: The initial question seems to be solved. Please vote on any contribution you want and pick the best answer and accept this. This will close this question and pays points to you and the answerers. Then start a new question. Provide an MCVE (DDL and data insert) and place a link to this question. – Shnugo Aug 16 '18 at 16:11
  • yes you're right. I posted this question with query & data samples: https://stackoverflow.com/questions/51882464/sql-get-value-for-a-specific-element-in-array Apologies for not being more specific 1st time around. – Tony Aug 16 '18 at 17:30
0

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
Alan Burstein
  • 7,770
  • 1
  • 15
  • 18
  • Hi Alan, thanks for sharing this, but like I mentioned earlier I cannot create functions or SP's in this DB. – Tony Aug 14 '18 at 21:26