multiple search words / one column in WHERE statement

[stevewaztech]

<img src=

http://www.cartoonnetwork.com/games/img/dl.jetsons.icon.gif><br><b>How

can I search for multiple keywords using a WHERE statement?</b><br><br>Here is a sample of my data. The column contains multiple values from an HTML form select box. <font color=red><br><FONT FACE=monospace>&nbsp;&nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;</font><FONT FACE=monospace>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Languages&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font></font><br><FONT FACE=monospace>&nbsp;&nbsp;&nbsp;Bob&nbsp;&nbsp;&nbsp;&nbsp;</font><FONT FACE=monospace>&nbsp;&nbsp;&nbsp;VBScript, Javascript, HTML&nbsp;&nbsp;</font><br><FONT FACE=monospace>&nbsp;&nbsp;&nbsp;Kim&nbsp;&nbsp;&nbsp;&nbsp;</font><FONT FACE=monospace>&nbsp;&nbsp;ASP, Cold Fusion, VisualIDev </font><br><FONT FACE=monospace>&nbsp;&nbsp;&nbsp;Jim&nbsp;&nbsp;&nbsp;&nbsp;</font><FONT FACE=monospace>&nbsp;&nbsp;&nbsp;Delphi, V++. C++, Clarion&nbsp;&nbsp;&nbsp;</font><br><br>When a user submits a query against this column, they are looking for multiple values (HTML AND / OR Javascript). I want to return rows that include any value that they search for. <br><br><b>Using a single where statement only returns rows that exactly match the search string </b><br>WHERE Languages = ‘HTML’&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;will only find a row that contains ‘HTML’<br>WHERE Languages Like ‘*HTML*’&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;will find all rows that contain the phrase ‘HTML’<br>But my users submit a search phrase like “HTML Javascript ASP’<br><br><b><font color=red>Is this the ONLY way to do it?</font></b><br>WHERE (Language Like '*HTML*' or Language Like '*Javascript*' or Language Like '*ASP*')<br><br>Similiarly:<br>WHERE (Language Like '*HTML*' and Language Like '*Javascript*' and Language Like '*ASP*')<br><br><br><br>If so, I can use javascript to build my WHERE statement, I was just wondering if there was an easier way to do it, aside from using the Full Text querying, and what is the limit length of a SELECT statement?<br><br><b>Thanks</b>

 

[JoernSaischek]

pass the search phrase to a sp,<br>parse it, build the where part of @mynewsearch<br>@mynewsearch=N'SELECT ..&nbsp;&nbsp;FROM ...'+CHAR(13)<br>@mynewsearch=@mynewsearch + N'WHERE '+@wherestr<br><br>and then execute sp_execute @mynewsearch<br><br>

 

[cdukes]

The best way to do this would be to redisign your data tables as storing the data in the current way is not very efficient and difficult to query as you have seen.<br><br>I would suggest two new tables, a lookup table to contain Languages (eg. 'VBScript', 'JavaScript' etc) with a primary key and a second table which has a one to many relationship between UserName and Languages and would be called UsersLanguages (contains UserName and LanguageId). <br><br>Each user then has an entry in the UsersLanguages for each language they know with foreign keys of the UserId and LanguageId.<br><br>Thus any queries to which languages the user knows will be as follows:<br><br>where languageId in ( 1, 2, 3, 4) <br><br>Where 1,2,3,4 relate respectively to the various languages in your language lookup table. If you want to make the query more readable, you could put a join on the languages table and include the Language name in the where clause:<br><br>Where LanguageName in ('VBScript', 'HTML'.......)<br><br>Hope This Helps<br><br><br>Chris Dukes<br>