Click here to Skip to main content
15,074,483 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
Sir

In SQL Server I have a table name called "Table1" and it has a column name "AreaCode". This column has data like...

Table1
-------

AreaCode

1,2,3,4
2,3,4
12,13,25
9,11,13,26


So i want that when a user enter 2 all records which contains 2 should be retrieved.
Is it possible in SQL ? Kindly help me Sir ?


thanking You

Sashibhusan Meher
Posted

Supposing that your Table1 has an identification field (I have named it Id), Here is another solution :


SQL
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.
   
Comments
Sashibhusan Meher 20-Nov-11 12:22pm
   
Thank you Sir.. its working.
Amir Mahfoozi 21-Nov-11 0:03am
   
You're welcome ;)
[no name] 21-Nov-11 1:38am
   
My 5. Its perfect. I realize about your answer, when my answer got down-voted. :) Probably OP has down-voted both your and mine, in the assumption that Vote-1 is First Class. :))
Amir Mahfoozi 21-Nov-11 2:30am
   
Thank you dear rais. I'm dubious about the OP is the downvoter. May be another idle person down voted us :-&
Try as below Query.
SQL
Select * from Table1
Where AreaCode LIKE '%,' + @Parameter + ',%' -- In middle
OR AreaCode LIKE @Parameter + ',%' -- In beginning
OR AreaCode LIKE '%,' + @Parameter -- At the end

OR AreaCode =  @Parameter -- Only Sngle value
   
v2
Comments
Amir Mahfoozi 21-Nov-11 2:32am
   
+5'ed. To be honest I was first planned to give this solution and it works well but then I decided to go work with Xml splitting tricks :-)
You need to use wild card search with something, refer these

SQL Server LIKE[^]
SQL Server Reference Manual - Wildcard Characters[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900