Supposing that your
Table1
has an identification field (I have named it
Id
), Here is another solution :
select Table1.Id, Table1.AreaCode from (
select Id , areaid from
(
SELECT
id,
CAST('<r>' + REPLACE(AreaCode, ',', '</r><r>') + '</r>' AS XML) AreaCodeXml
FROM Table1
) NewArea
CROSS APPLY (
SELECT
AreaId.value('.', 'int') AreaId
FROM NewArea.AreaCodeXml.nodes('r') AS AreaCodes(AreaId)
) Splited
where AreaId = 2
) res1
inner join Table1 on res1.id = Table1.id
Please replace 2 with a variable or parameter ...
Hope it helps.