Searching Database query

[craizie]

does anyone know how to search all the fields in a database table for a specific string. I know how to search specific fields but i'd like to know how to search the entire table.

For example let's say my table named 'employees' has the fields 'firstname, lastname, ssn, city, address, state' all type var.

"select firstname from employees where firstname like '%john%' order by firstname"

Now that query only searches the firstname field for anything that contains john. Is there a way i can search all the fields without having to specify each column individually.

Thanks for the help.

 

[Adhie]

Sorry, this .asp messy code and only similar with you problem, and still got some more function below, but could be useful.

q = Replace(Request.QueryString("q&quot, "'", "''&quot
arrq = Split(q, " &quot
q = Replace(q, "''", "'&quot

page = Request.QueryString("page&quot
if page= "" or not IsNumeric(Page) then Page = 1 else Page = CInt(Page)


For i = 0 to UBound(arrq)
If i <> 0 Then subSQLStr = subSQLStr &&quot; OR &quot;
subSQLStr = subSQLStr &&quot;tblClassification.clsName Like '%&quot;& arrq(i) &&quot;%' OR tblCompany.comName Like '%&quot;& arrq(i) &&quot;%' OR tblCompany.comBusiness Like '%&quot;& arrq(i) &&quot;%'&quot;
Next

SQLStr = &quot;SELECT DISTINCT tblCompany.comId, tblCompany.comName &quot;&_
&quot;FROM (tblClassification INNER JOIN tblComClass ON tblClassification.clsId=tblComClass.clsId) INNER JOIN tblCompany ON tblComClass.comId=tblCompany.comId &quot;&_
&quot;WHERE (tblCompany.comActive = 1) AND (&quot;& subSQLStr &&quot ORDER BY tblCompany.comName&quot;
RS1.Open SQLStr, 3, 1, 0

 

[TonyKhela]

You could create Full Text indexes on the fields on the table. Then use WHERE CONTAINS(*,'John')

This is also much faster then character comparisons.

Hope it helps .
TK