1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| DECLARE @article TABLE
(
ID tinyint NOT NULL IDENTITY PRIMARY KEY
, LIB char(4) NOT NULL
, CODE varchar(8) NOT NULL UNIQUE
)
INSERT INTO @article (LIB, CODE)
VALUES ('ART1', 'POMME')
, ('ART2', 'POIRE')
, ('ART3', 'PRUNE')
, ('ART2', 'FRAISE')
, ('ART2', 'APOLLON')
DECLARE @txt varchar(1024) = 'ABRICOT_PRUNE_CERISE_APOLLON_TEST'
SELECT A.*
FROM @article AS A
INNER JOIN (
SELECT N.article_code.value('.', 'varchar(8)') AS article_code
FROM (VALUES(CAST('<v>' + REPLACE(@txt, '_', '</v><v>') + '</v>' AS xml))) AS AC(article_code)
CROSS APPLY AC.article_code.nodes('/v') AS N(article_code)
) AS AC(article_code)
ON AC.article_code = A.CODE |
Partager