How to retrieve part of string writen with , separator in sql server ?

Question

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 19-Nov-11 14:53pm

Sashibhusan Meher

Add a Solution

3 solutions

Solution 2

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

Posted 19-Nov-11 20:35pm

RaisKazi

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 :-)

Solution 1

You need to use wild card search with something, refer these

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

Posted 19-Nov-11 17:54pm

thatraja

Add a Solution

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

Amir Mahfoozi · Accepted Answer · 2011-11-20T01:12:00

Solution 3

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.

Posted 20-Nov-11 1:12am

Amir Mahfoozi

Comments

Sashibhusan Meher 20-Nov-11 12:22pm

Thank you Sir.. its working.

Amir Mahfoozi 21-Nov-11 0:03am

You're welcome ;)

RaisKazi 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 :-&

How to retrieve part of string writen with , separator in sql server ?

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

How to retrieve part of string writen with , separator in sql server ?

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us