Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL query to search strings

Status
Not open for further replies.

nirajj

Programmer
Mar 12, 2003
103
US
Hi,

I have a table with a field that will have values as follows:

"Value1, Value2, Value 3, Value 4"
"Value2, Value1, Value 6"
"Value 3, Value 4, Value, 5, Value 1"

Basically same set of words repeated in random order.

Search Scenario I : search string "Value1, Value2" should return 1st and 2nd row.
Search Scenario II : "Value 2, Value1" should also return 1st and 2nd row.
Search Scenario III : search string "Value1, Value3" should return 3rd row.


Now I am not sure how to write the SQL for this.

Any insight would be really great.

Thank you very much.



 
you shouldn't store multiple pieces of info in a single field, breaks normalization rules. If possible, rethink the design and only put a single piece of info in each field.


Leslie
 
Thats true Leslie. I totally agree with you.

But the problem is that the table already exists with lots of records. Normalization wasnt done at the initial design. I am actually thinking about writing some program that will populate values into new tables.

But then if there is a way out to write a query without changing tables, then I would like to do that. Changing tables means a lot of work. I am sure you know that.

Thank you.
 
One way:
Code:
...
WHERE [yourfield] like '*Value1*' AND [yourfield] like '*Value2*'
...
You could make the selection parameters in a query or get them from a form.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill
 
search string "Value1, Value2"
WHERE [relevant field] Like '*Value1*' Or [relevant field] Like '*Value2*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top