Search value in column which is pipe separated in sql

Question

I want to search value which is pipe-separated in column. See below.

Column1 
1
1|2|23
2
6
6|12

I want to search 2 in all rows so it will return below rows

Column1
1|2|23
2

Can anyone please tell me how can we achieve this?

Answer 1

You can use like:

where '|'+column1+'|' like '%|2|%'

By including the extra delimiters, you avoid having "2" match "23".

Answer 2

How about something like

DECLARE @SearchVal VARCHAR(50) = '2'

SELECT *
FROM YourTable 
WHERE '|' + Column1 + '|' LIKE '%|' + @SearchVal + '|%'

Have a look at the below demo

SQL Fiddle DEMO

Further to this solution, as @marc_s stated above, this is typically the end result of a design gone wrong. I would urge you to step back and rething the solution, as this will only impact you performance/maitinance/sanity wise further down the line.