Sql pour comparer une string délimitée à une table de valeurs connue pour find des discordances

J'essaye d'écrire une procédure stockée qui vérifiera si une string contient des valeurs qui sont dans une autre table.

Je ne suis pas en contrôle de la TableA et datatables stockées dans cette colonne Packages théorique sont un set de mots délimités.

Pour démontrer, TableA contient une colonne, Packages. Cette colonne contient une string de packages délimitée par des carets, EG A^B signifierait que cette rangée a 2 packages.

TableB est un contrôle table I qui répertorie les packages disponibles.

Comme nous pouvons le voir, TableA a des lignes qui n'ont pas de package correspondant; D par exemple.

TableA

 ID | Packages ============ 1 | A 2 | A^B 3 | C 4 | A^B 5 | A^C 6 | D 7 | C^D

TableB

 ID | Package ============ 91 | A 92 | B 93 | C

J'essaye d'écrire un manuscrit qui comparera les packages dans la TableA contre la list connue de Paquet dans la TableB et choisira n'importe quelle rangée de la TableA qui n'a pas de package de soutien.

Une fois couru, je vise à returnner un résultat similaire à celui-ci,

 TableAID | PackageNotSupported ================================ 6 | D 7 | D

Je ne peux pas vraiment montrer ce que j'ai essayé jusqu'ici parce que je ne sais pas par où commencer. J'ai une fonction dans la database qui splita une string délimitée dans une table. Je peux l'utiliser contre datatables xx^xx^xx mais je n'arrive pas à comprendre comment joindre / comparer les strings.

J'ai essayé la solution trouvée dans ce post, Comment comparer plusieurs valeurs dans une colonne contre une string délimitée dans une procédure stockée, mais n'a pas pu get le résultat que je visais. En fin de count SQL n'est pas mon fort et je n'ai pas la meilleure compréhension de la solution proposée dans ce post.

Option 1 – Sans une fonction Parse / Split

 Declare @TableA table (ID int,Packages varchar(25)) Insert Into @TableA values (1 ,'A'), (2 ,'A^B'), (3 ,'C'), (4 ,'A^B'), (5 ,'A^C'), (6 ,'D'), (7 ,'C^D') Declare @TableB table (ID int,Package varchar(25)) Insert Into @TableB values (1 ,'A'), (2 ,'B'), (3 ,'C') Select A.ID ,Package=B.RetVal From @TableA A Cross Apply ( Select RetSeq = Row_Number() over (Order By (Select null)) ,RetVal = LTrim(RTrim(Bivalue('(./text())[1]', 'varchar(max)'))) From (Select x = Cast('<x>' + replace((Select replace(A.Packages,'^','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A Cross Apply x.nodes('x') AS B(i) ) B Left Join @TableB C on B.RetVal=C.Package Where C.Package is null

Résultats

 ID Package 6 D 7 D

Option 2 – Avec une fonction Parse / Split

 Select A.ID ,Package=B.RetVal From @TableA A Cross Apply [dbo].[udf-Str-Parse](A.Packages,'^') B Left Join @TableB C on B.RetVal=C.Package Where C.Package is null

The Parse UDF si intéressé

 CREATE FUNCTION [dbo].[udf-Str-Parse] (@Ssortingng varchar(max),@Delimiter varchar(25)) Returns Table As Return ( with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)), cte2(N) As (Select Top (IsNull(DataLength(@Ssortingng),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ), cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Subssortingng(@Ssortingng,tN,DataLength(@Delimiter)) = @Delimiter), cte4(N,L) As (Select SN,IsNull(NullIf(CharIndex(@Delimiter,@Ssortingng,sN),0)-SN,8000) From cte3 S) Select RetSeq = Row_Number() over (Order By AN) ,RetVal = LTrim(RTrim(Subssortingng(@Ssortingng, AN, AL))) From cte4 A ); --Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/ --Much faster than str-Parse, but limited to 8K --Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',') --Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')