Concatobate = Concatenate And don't for get your wildcards on the like '%#search#%'
Patrick McGeehan Applications Developer DIT <CF_DIT>#mcg#</CF_DIT> -----Original Message----- From: Patrick McGeehan [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 1:11 PM To: CF-Talk Subject: RE: search for a string across columns Concatobate. What type of db you are using says what the concat operator is. The below is for oracle. <cfquery name="getSearchItem" datasource="dpch"> SELECT recipients_lname,recipients_fname,recipients_mi,leaf_number FROM giving_leaves WHERE line_1 || line_2 || line_3 ||line_4 || line_5 || line_6 LIKE '#s#' </cfquery> You would just have to worry about the spaces because in your example line_1 and line_2 would concat to "I need togo to the bathroom", so depending on how it is set up over all maybe concat with a space between, like this WHERE line_1 || ' ' || line_2 || ' ' || line_3 || ' ' || line_4 || ' ' || line_5 || ' ' || line_6 LIKE '#s#' Patrick McGeehan Applications Developer DIT <CF_DIT>#mcg#</CF_DIT> -----Original Message----- From: Daniel Kessler [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 12:11 PM To: CF-Talk Subject: search for a string across columns I have a db that contains 6 similar columns: line_1, line_2 , line_3 , line_4 , line_5 , line_6 I want to have a search that searches across columns. While I can search them individually, it would be better if they were combined before the LIKE was done so that if I had: line_1="I need to" line_2="go to the bathroom" and I searched "need to go", it would return a hit. I'm not sure how to approach this but while I have a beginning, I know that it is incorrect because it's the first type of search, searching each line individually. <cfquery name="getSearchItem" datasource="dpch"> SELECT recipients_lname,recipients_fname,recipients_mi,leaf_number FROM giving_leaves WHERE line_1 LIKE '#s#' OR line_2 LIKE '#s#' </cfquery> thanks for the help. -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188561 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
