Stack Exchange
TSQL stuff for xml path and sub query
I've to replace csv datas in column by correspondant id always in csv format
I've a problem with this query :
select t0.code , t0.categories, t0.departement, (
    SELECT Stuff((
        SELECT N', ' + CONVERT(varchar, id_categorie) FROM tcategories t1 WHERE t0.departement = t1.departement COLLATE French_CI_AI and categorie IN (t0.tcategories)  
    FOR XML PATH(''),TYPE).value('text()[1]','varchar(max)'),1,1,N'')) as id_colonne  
FROM #codes_reductions t0 where categories is not null 

Here is the result :

code    |    categories    |   departement    |     id_colonne
AIRSTREAM  | 'A','B','BA'  |    JMQ           |     NULL

If I replace 'and categorie IN (t0.tcategories)' by and categorie IN ('A','B','BA')  the query works good
Here is the result :

code    |    categories    |   departement    |     id_colonne
AIRSTREAM  | 'A','B','BA'  |    JMQ           |     128, 129, 260

I tryed to use COLLATE French_CI_AI on my column, but without success. Any idea ?

... categorie IN (t0.tcategories) ....

The cause of your problem is that categorie column stores those values ('A','B','BA') as a single value not as an array / list / table of values. So, SQL Server compares two strings thus s1 IN (s2) which is equivalent to s1 = s2 => A IN ('A','B','BA') <=> A = 'A','B','BA'.
Example (note: double single quotes ('') are used to define an empty string while four single quotes ('''') are used to define a string with a single quote: SELECT '''' --> '):
DECLARE @categories VARCHAR(1000);

SET     @categories = '''A'',''B'',''BA'''
SELECT @categories AS ColA, CASE WHEN 'A' IN (@categories) THEN 'TRUE' ELSE 'FALSE' END AS Col2
/*
ColA         Col2
------------ -----
'A','B','BA' FALSE
*/

The solution on short term is to use one of following conditions:
C#1 (if categorie contains single quotes):  ... t0.tcategories LIKE '%' + categorie + '%' ....

C#2 (when categorie doesn't contains single quotes):  ... t0.tcategories LIKE '%''' + categorie + '''%' ....

Example:
DECLARE @categories VARCHAR(1000);

SET     @categories = '''A'',''B'',''BA'''
SELECT @categories AS ColA, CASE WHEN @categories LIKE '%''A''%' THEN 'TRUE' ELSE 'FALSE' END AS Col2
/*
ColA         Col2
------------ -----
'A','B','BA' TRUE
*/

Second note: this works when every separate value from t0.tcategories column doesn't includes single quote(s) (example: 'B'A' / 'B''A' ).
On medium/long term, you should store separately every single value from tReduction.tCategories column using another table :
CREATE TABLE dbo.ReductionCategory (
... pk ...,
ReductionCode INT NOT NULL REFERENCES dbo.tReduction(ReductionCode), -- FK
CategoryCode INT NOT NULL REFERENCES dbo.tCategories(CategoryCode) -- FK  
)

Thus, condition becomes 
... categorie /*CategoryCode*/ 
IN (
    SELECT rc.CategoryCode FROM dbo.ReductionCategory rc 
    WHERE rc.ReductionCode = t0.ReductionCode
) ....


If you have any questions then feel free to ask.
