sql - How to find specific VALUE to specific STRING location in different strings

Question

I have a sting like this
,x,x,y,x,x,O,x,y
that matches to its values in an other sting like this
0~1~b~~z~XY~1~7.
The value "O" can switch its position in the string, as will the value in the other string.
The value "O" is in the 6th position so the expectet result would be "XY".
The first string always begins with a "," before each value. The second string starts first with the value followed by the "~".
"O" is a given value which will not change, so I always have to finde the given value for the position wher "O" is, in the second string.

This ist what I am expexting:

,x,x,y,x,x,O,x,y
0~1~b~~z~XY~1~7

O=XY


,x,O,y,x,x,y,x,y
0~1~b~~z~XY~1~7

O=1

Thank you.

Answer 1

Grab a copy of DelimitedSplit8K then you can do this:

DECLARE @string1 VARCHAR(1000) = ',x,x,y,x,x,O,x,y',
        @string2 VARCHAR(1000) = '0~1~b~~z~XY~1~7';

DECLARE @search VARCHAR(1000) = 'O'; -- best as a variable/parameter

SELECT *
FROM dbo.delimitedSplit8K(@string2,'~') AS s
WHERE s.itemNumber = 
(
  SELECT TOP (1) s2.itemNumber -- TOP (1) until we know about dupicates
  FROM   dbo.delimitedSplit8K(@string1,',') AS s2
  WHERE  s2.item = @search
)-1;

Returns:

ItemNumber           Item
-------------------- -------
6                    XY