How to use IN to compare a single value with a comma separated field in another column 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'
)
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)