troubleshooting Question

How do I write a SELECT query to find records having an item in a complex field that appears in another field

Avatar of dwoolley3
dwoolley3Flag for United States of America asked on
ASP.NETMicrosoft SQL Server 2005Microsoft SQL Server 2008
10 Comments2 Solutions262 ViewsLast Modified:
I would like to have a SELECT query (or statements) in SQL Server 2005 that selects records where one of the items in a Complex field (items separated by commas) matches another field in a view. When the Complex field contains only one item, it is trivial:

SELECT *
  FROM table
where Complex IN
(SELECT SMS
   FROM ComplexView)

Let's say that SMS could be potentially be some of these 4 values: A, B, ABC, BD
And let's say that Complex could contain any of those 4 values or a combination of them, separated by commas:
A
B
ABC
BD
A,B
A,ABC
A,BD
B,ABC
B,BD
ABC,BD
A,B,ABC
B,ABC,BD
A,B,ABC,BD

Thus, I want a record having Complex field of "A,BD" to be displayed if one of the SMS values is "A" or "BD"; however, the record would not display if SMS is only the value of "B".
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros