<div>
Arif it is working but can it is possible in simple way....................??<br />
<br />
I want to search from comma seprated lists. if it is possible using IN , LIKE........
</div>


<div>
No you can not directly use like because when you use like it may return incorrect record.<br />
<br />
E.g.<br />
<br />
If you write a query filter<br />
<br />
CSV_List.like '%2%'<br />
&nbsp;<br />
it will also return &quot;82&quot; which is not correct.
</div>


<div>
If it is about searching a single value, you can add commas to start and end of the table field, and then use like with delimiters:<br />

<pre><code class="code-1 nolinks" id="code-20-41524742-1"><span>where ','+csv_list+',' like ',2,'</span>
</code></pre>
but that is slow - no index can be used for csv_list, and so it results in a full table scan. Nothing you want to do with millions of rows.
</div>


<div>
You are not very well versed with dataabses, if you don't understand why there is no simple solution. SQL is made for acting on datasets, but you put your datasets of several CSV rows into a single nvarchr field.<br />
<br />
To act on the atomic values of these CSV rows, this nvarchar text has to be split into the single values first, and that's what Arifs code does.<br />
<br />
If you split your data into single values. you could skip some of that code.<br />
<br />
To show what you ask even in a simpler scenario of the varchar field only containing one row, there is no simple LIKE to filter for 2 or 4 in rows like '2,3,4,' or '4,5,6' or even jus t2 or 4, filtering for two values already means 2 LIKE clauses, one looking for 2 contained and one for 4 cointained. You also doin't want to find any number ending in 2 or 4 or beginning with 2 or 4, so you actually look for ,2, or ,4, with or without spaces. the single digit surrounded by comma. And that only covers numbers in the middle of a row. To get a 2 or 4 as start or end of the list, the simplest idea is to prepend and append an extra comma, making any item of the list a middle item, like Qlemo suggested.<br />
<br />
You don't even have started to think about what you're asking, if you think, this is easy code. You don't get, that code acting on very atomic things like bytes is no intelligence. It's easy to explain, what you want, but it's not easy to do. It's fairly easy to answer this with code as Arif did. <br />
<br />
There are tasks even harder describable in a simple sentece, eg to find a certain person in photos. You have a natural understanding, that this needs much more code, than a simple query Your task is about finding two numbers in lists of numbers, which is &nbsp;asubtask, once the major problem of identifying persons (and eg replacing them with a number) already has been achieved. But this rest taks still means splitting up the lists both of data and filter, to match the single values.<br />
<br />
Bye, Olaf.
</div>


<div>
Just to illustrate the difficulties further: <br />
<br />
Let's simply take the idea of Qlemo and add commas, then a thing worth trying to match 2 AND 4 would be somefield LIKE '%,2,%,4,%', but that already fails, when somefield contains 2,4 without a number inbetween. Changing to LIKE '%,2%4,%' means you match things like '1,24,53', too, as the % between 2 and 4 can also mean nothing at all. Keeping only one commma as in LIKE '%,2,%4,%' would still match things like '1,2,34,56'.<br />
<br />
Besides that difficulties you're uncapable to search for 2 OR 4 in one LIKE clause, because you can't design a pattern where either one is optional. <br />
<br />
Now taking your IN idea. IN needs a list of separate values you can query 2 IN(1,2,3,4). but not 2 IN '1,2,3,4'. To find substrings you'll need CHARINDEX and forget about going that route, though it's part of the solution spiltting at commas.<br />
<br />
What may help a bit better are regular expressions, a thing less supported in the whole Microsoft world. If you could do regexp matching, the regular expression ^(2|4)$ will find lines containing either 2 or 4 or both, but still not cover excluding 12 or 24 or 42 or any other number conatining 2 or 4. <br />
<br />
What's positive in regular expressions is, you have the OR operator | here, and you have symbols for line begin ^ and line end $ to use in your pattern. Now find an extension to T-SQL supporting regular expressions and you can make such filters easier. But then don't blame us regular expressions in themselves are a topic with a steep learning curve.<br />
<br />
Bye, Olaf.
</div>


<div>
Seriously? You prefer the more complex CTE + charindex approach?
</div>


<div>
Qlemo,<br />
<br />
one thing you didn't consider in your simple solution is the csv field contains multiple lines of several value lists. so pre/suffixing with commas needs to be done multiple times. Doing that, and then needing more like clauses and correct like clauses makes it harder to set the filter. So your idea expanded also will get more complex.<br />
<br />
It'd be best to generate new tables and split the data by lines and then by commas to get to the atomic number values and do other queries.<br />
<br />
Bye, Olaf.
</div>


<div>
Olaf, I cannot see from the question how the CSV field should contain multiple lines. One row, one list. And that would work, as long as you search a <u>single</u> value.
</div>


<div>
<div class="content wysiwyg-content">
I have comma separated column and data look as :<br />
<br />
CSV_List (Nvarchar(100))<br />
2,4<br />
2,4,10<br />
2<br />
4<br />
4,10,15,21 etc.<br />
<br />
I want to filter as , <br />
if user search for 2 it return following result<br />
CSV_List (Nvarchar(100))<br />
2,4<br />
2,4,10<br />
2<br />
<br />
if user search for 2,4 it return following result<br />
CSV_List (Nvarchar(100))<br />
2,4<br />
2,4,10<br />
2<br />
4<br />
4,10,15,21<br />
<br />
I am trying like operator but not return expected result.please provide me filter query.
</div>
</div>


I have comma separated column and data look as :

CSV_List (Nvarchar(100))
2,4
2,4,10
2
4
4,10,15,21 etc.

I want to filter as , 
if user search for 2 it return following result
CSV_List (Nvarchar(100))
2,4
2,4,10
2

if user search for 2,4 it return following result
CSV_List (Nvarchar(100))
2,4
2,4,10
2
4
4,10,15,21

I am trying like operator but not return expected result.please provide me filter query.

SQL Filter Query

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.