How to use IN to compare a single value with a comma separated field in another column

Question

I have a table that looks like this. let's call it table1:

|attributeMask |
|--------------|
| ,300,302,400 | 
| ,301,500     | 
| ,900,876     |

These are varchar values.

Then I have another table2:

 columnNumber  | 
|------|
| 300  | 
| 500  | 
| 900  |

These ones are int.

What I need is a query to compare columnNumber with values inside attributeMask.

For example, I need to find inside attributeMask, rows that has 300 (for each row. the second columnNumber is 500, and I will compare this 500 with the respective row from it's own attributeMask that can be ,299,34,500). I'm trying to use FOR XML PATH or other functions but I have no idea what to do.

This is the entire query that i could create until now:

select  top 3  
    t1.[Name]
   ,t1.ColumnNumber
   ,t3.AttributeMask 
   ,t3.changeData 
   ,t3.CreatedOn
   ,t3.ObjectTypeCode
   ,t3.UserId
from metadataschema.attribute     t1
inner join  metadataschema.Entity t2
    on t1.EntityId=t2.EntityId
inner join AuditBase              t3
    on t2.ObjectTypeCode=t3.ObjectTypeCode
where t2.ObjectTypeCode=1 
//this is wrong. but is this part that I need to fix
and convert(varchar,t1.ColumnNumber) in (select t3.AttributeMask from 
AuditBase)
//until here
and t3.CreatedOn BETWEEN '20190902' AND '20190913' 
and t1.[Name] in
(
'address1_postalcode','smart_caixapostal','smart_tipologradouro','smart_complementoprincipal','smart_estado','smart_logradouroprincipal','smart_bairroprincipal',
'new_regiao','smart_numero','address1_city','smart_pais','smart_validarendereco','smart_cepcobranca','smart_caixacobranca','smart_validarendereco_cob',
'smart_tipologradourocobranca','smart_logradourocobranca','smart_numerocobranca','smart_complementocobranca','smart_bairrocobranca','smart_municipiocobranca',
'smart_estadocobranca','territoryid','smart_paiscobranca','smart_cepmkt','smart_logradouromkt','smart_complementomkt','smart_ufmkt','smart_tipodelogradouromkt',
'smart_bairromkt','smart_ddidddmkt','smart_caixapostalmkt','smart_numeromkt','smart_municipiomkt','smart_telefonemkt'
)

score 0 · Answer 1 · answered Sep 17 '19 at 21:29

There is a way to do this. However, I highly recommend that you reconsider using comma-separated values within a column. If you ABSOLUTELY must do it this way, you can use the LIKE operator with wildcards. This will enable you to search the attribute mask string for values. Expect terrible performance when looking up many values.

inner join AuditBase              t3
    on t2.ObjectTypeCode=t3.ObjectTypeCode
    and t3.AttributeMask LIKE '%' + convert(varchar,t1.ColumnNumber) + '%'
AuditBase)

score 0 · Answer 2 · answered Sep 17 '19 at 23:40

Going by the sample data you've provided, an entry is stored as <comma><value>. So, matching needs to reflect that:

t3.AttributeMask LIKE '%,' + CAST(t1.ColumnNumber AS VARCHAR(10)) + '%'

(CAST vs. CONVERT is ultimately a matter of taste, but explicitly specifying a VARCHAR's length removes the context-specific nature of the default.)

However, the above falls foul of cases like matching for 101 when the data contains only 1011. So, we need the following (or something else to solve the same problem):

(t3.AttributeMask LIKE '%,' + CAST(t1.ColumnNumber AS VARCHAR(10))
    OR (t3.AttributeMask LIKE '%,' + CAST(t1.ColumnNumber AS VARCHAR(10)) + ',%'

This assumes AttributeMask will contain only commas and digits. If that, or indeed, a leading comma can't be assumed, then you will need to do a spot of data cleansing each time (something best avoided if possible of course):

INNER JOIN AuditBase              t3
ON t2.ObjectTypeCode=t3.ObjectTypeCode
WHERE t2.ObjectTypeCode=1 
    AND ',' + REPLACE(t3.AttributeMask, ' ', '') + ','
        LIKE '%,' + CAST(t1.ColumnNumber AS VARCHAR(10)) + ',%'

Always prepending and appending a comma in this version is harmless given the nature of the LIKE test.

How to use IN to compare a single value with a comma separated field in another column

2 Answers2