Looking for occurrence of substrings in another string

Question

I have two MS SQL tables:

screenshot of plain text, for some reason

I have to say with a loop if ALL the character of the strings of table one are contained in the string of table two.

E.g.

 (_a_b_c_d_) is contained in (_a_c_b_d_) 

 (_a_b_) is contained in (_g_b_a_)

 (_a_f_d_) is not contained in (_a_c_b_d_)

Hope you can help!

Thanks

--EDIT

Underscore are delimiters

Here what result can look like:

enter image description here

Sample data with rows from *both* tables would help explain what you are trying to do. It is not clear what results you want. — Gordon Linoff, Apr 06 '16 at 21:44
checksum will not work SELECT CHECKSUM ('_a_b_c'), CHECKSUM('_b_c_a') produces different output, but OP wants this to be true — Nikki9696, Apr 06 '16 at 21:51
Gonna be ugly. I gotta go, but this is where I'd start - split out the chars into rows and use contains. Here's a post. http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — Nikki9696, Apr 06 '16 at 21:58
Break the strings and then JOIN on like or something similar http://sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/6365 — Mihai, Apr 06 '16 at 21:59
You appear to have the wrong data format. If you are storing lists in strings, then you should fix the underlying data to use junction tables. — Gordon Linoff, Apr 06 '16 at 22:04
Another related question may help: http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x — Stefan Steinegger, Apr 07 '16 at 09:52

score 0 · Answer 1 · answered Apr 07 '16 at 06:55

You can try this:

DECLARE @xml1 xml, @xml2 xml

SELECT @xml1= (
SELECT (CAST('<b id="'+cast(id as nvarchar(2)) + '"><a>' + REPLACE(string,'_','</a><a>') + '</a></b>' as xml))
from table1
FOR XML PATH('')
)

SELECT @xml2= (
SELECT (CAST('<b id="'+cast(id as nvarchar(2)) + '"><a>' + REPLACE(string,'_','</a><a>') + '</a></b>' as xml))
from table2
FOR XML PATH('')
)

;WITH res1 AS (
SELECT  t.v.value('../@id','int') as id,
        t.v.value('.','char(1)') as chars
FROM @xml1.nodes('/b/a') as t(v)
WHERE t.v.value('.','char(1)') !=''
), res2 AS (
SELECT  t.v.value('../@id','int') as id,
        t.v.value('.','char(1)') as chars
FROM @xml2.nodes('/b/a') as t(v)
WHERE t.v.value('.','char(1)') !=''
), cte1 AS (
SELECT distinct id, (SELECT '_' + r.chars FROM res1 r WHERE r.id = r1.id ORDER BY r.id, r.chars FOR XML PATH('')) + '_' as string
FROM res1 r1
), cte2 AS (
SELECT distinct id, (SELECT '_' + r.chars FROM res2 r WHERE r.id = r2.id ORDER BY r.id, r.chars FOR XML PATH('')) + '_' as string
FROM res2 r2
)

SELECT t1.id as table1id, t2.id as table2id
FROM cte1 t1
INNER  JOIN cte2 t2 ON  t2.string LIKE '%'+t1.string+'%' --t1.string LIKE '%'+t2.string+'%' 
ORDER BY t1.id

Results:

table1id    table2id
----------- -----------
1           1
2           1
2           4
4           1
4           3

(5 row(s) affected)

As I can see _a_b_ is conteined in _a_c_b_d_.

Looking for occurrence of substrings in another string

1 Answers1