I have following values in 4 rows and 1 column in SQL Server 2005.
7,75,8,95,55
8,75,8,95,66
6,75,86,7,55
74,75,84,95,55
If I pass value 7 in SQL query, I need to get exact rows which have value 7.
-
4Comma-separated value lists inside a single column is a horribly bad idea.... don't do it! You're violating the first normal form of relational database design, and it makes searches like yours here hard and tricky.....marc_s– marc_s2012-04-30 06:52:20 +00:00Commented Apr 30, 2012 at 6:52
Add a comment
|
2 Answers
Presumably, you want '7' matched as a number, but presumably not other numbers that contain the digit '7' (e.g., 75).
Anything you do with this is going to be pretty ugly, so you'd be much better of simply changing the table if at all possible. I suppose if you have no choice, you could do something like:
select whatever from your_table where
your_column like '7,%' or
your_column like '%,7,% or
your_column like '%,7'
The first covers when the '7' is the first number in the group. The second covers when it's in the middle of the group, and the third covers when it's the last in the group.
This is ugly, and unless you're dealing with very small amounts of data, it's almost certain to be excruciatingly slow as well. I'll repeat: you'll really be a lot better off fixing the basic design instead.
2 Comments
CapelliC
I was posting a similar answer... I think here we miss a row with the standalone '7'
Jerry Coffin
@chac: Yes, if there's a possibility of a proper field that just has one value, then we'd want to add
or your_column = '7'.declare @T table
(
Col varchar(20)
)
insert into @T values
('7,75,8,95,55'),
('8,75,8,95,66'),
('6,75,86,7,55'),
('74,75,84,95,55')
declare @Val varchar(10)
set @Val = '7'
select Col
from @T
where ','+Col+',' like '%,'+@Val+',%'
