July 13, 2015 at 2:12 pm
Consider the following data:
create table #test
(id int
,color varchar(20)
)
insert into #test
(id, color)
values
(1, 'blue'),(2, 'red'),(3,'green'),(4,'red,green')
In this example, if I wanted to run a query to select any records that had red in the color field, how would I do that? Not the one with only red, but a query that would give me both record number 2 and record number 4.
Thanks!
July 13, 2015 at 2:22 pm
robert.wiglesworth (7/13/2015)
Consider the following data:create table #test
(id int
,color varchar(20)
)
insert into #test
(id, color)
values
(1, 'blue'),(2, 'red'),(3,'green'),(4,'red,green')
In this example, if I wanted to run a query to select any records that had red in the color field, how would I do that? Not the one with only red, but a query that would give me both record number 2 and record number 4.
Thanks!
This isn't optimal for performance but it works
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#test') IS NOT NULL DROP TABLE #test;
create table #test
(id int
,color varchar(20)
)
insert into #test
(id, color)
values
(1, 'blue'),(2, 'red'),(3,'green'),(4,'red,green');
SELECT
*
FROM #test T
WHERE T.color LIKE '%red%';
Results
id color
----------- --------------------
2 red
4 red,green
July 13, 2015 at 2:35 pm
Ok, yes, that's true. But what if I wanted to do something like this:
select * from #temp t where t.color in ('red','blue')
and still get record number 4 because red is in there?
July 13, 2015 at 2:45 pm
robert.wiglesworth (7/13/2015)
Ok, yes, that's true. But what if I wanted to do something like this:select * from #temp t where t.color in ('red','blue')
and still get record number 4 because red is in there?
That will not work, the IN requires a definite and complete list of values, no such thing as IN PARTIAL.
😎
July 13, 2015 at 4:03 pm
So then you are saying that what I am trying to do is just not possible?
July 13, 2015 at 4:28 pm
You may want something like this:
SET NOCOUNT ON;
create table #test
(id int
,color varchar(20)
)
insert into #test
(id, color)
values
(1, 'blue'),(2, 'red'),(3,'green'),(4,'red,green');
SELECT
T.*
FROM
#test T
cross apply (select Item from dbo.DelimitedSplit8K(T.color,','))ca(Item)
WHERE
ca.Item in ('red');
SELECT
T.*
FROM
#test T
cross apply (select Item from dbo.DelimitedSplit8K(T.color,','))ca(Item)
WHERE
ca.Item in ('blue','red');
Code for the delimited split routine is attached.
July 14, 2015 at 1:06 pm
Ok, thanks. That seems to work. But it seems complicated. Opinions on this please....
If I am able to edit how the records in this table get entered, should I change it? Let's say that right now the values for the table in this example are entered by a user on a web form. For the color options, they are given check boxes and can enter one or more color options. Currently, the form will take the options and enter them into the 'color' field as comma separated values, just as we have in the example.
Should I change the data entry process to enter a single record for each color entry? Meaning that I would add another field that would tie 2 records together somehow and have the entry process create something like this if the user selects more than one color:
ID entryid color
1 1 blue
2 2 red
3 3 green
4 4 red
5 4 green
July 14, 2015 at 1:11 pm
robert.wiglesworth (7/14/2015)
Ok, thanks. That seems to work. But it seems complicated. Opinions on this please....If I am able to edit how the records in this table get entered, should I change it? Let's say that right now the values for the table in this example are entered by a user on a web form. For the color options, they are given check boxes and can enter one or more color options. Currently, the form will take the options and enter them into the 'color' field as comma separated values, just as we have in the example.
Should I change the data entry process to enter a single record for each color entry? Meaning that I would add another field that would tie 2 records together somehow and have the entry process create something like this if the user selects more than one color:
ID entryid color
1 1 blue
2 2 red
3 3 green
4 4 red
5 4 green
The methodology Lynn posted is the best way to handle this. Does it seem overly complicated? Perhaps, but that is because your data structures are not properly normalized. When you store multiple values in a single column you are violating 1NF. Since the structures are not defined with normalization you have to resort to complicated queries to unravel the poor design decisions. It would be much easier if you had only properly normalized table structure which you suggested already.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 14, 2015 at 1:19 pm
True about the normalization. What I would like to do is break it up into a couple of tables, an entry table and entry details. But I am working with an existing table structure and they are not paying enough for the level of work that would be the real fix. They just want a 'band-aid'.
I guess you get what you pay for...
thanks for all the input!
July 14, 2015 at 1:22 pm
robert.wiglesworth (7/14/2015)
Ok, thanks. That seems to work. But it seems complicated. Opinions on this please....If I am able to edit how the records in this table get entered, should I change it? Let's say that right now the values for the table in this example are entered by a user on a web form. For the color options, they are given check boxes and can enter one or more color options. Currently, the form will take the options and enter them into the 'color' field as comma separated values, just as we have in the example.
Should I change the data entry process to enter a single record for each color entry? Meaning that I would add another field that would tie 2 records together somehow and have the entry process create something like this if the user selects more than one color:
ID entryid color
1 1 blue
2 2 red
3 3 green
4 4 red
5 4 green
Yes, absolutely yes. That's part of the normalization that Sean mentioned. Just be sure that you're not duplicating excessive data, you might need a separate table if duplication occurs. Here's a basic article on normalization: http://www.sqlservercentral.com/articles/T-SQL/normalization/584/
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply