Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

SQL query with multiple contains question.

Contributor ,
Sep 12, 2019 Sep 12, 2019

I am trying to build a query where the end-user can search a field in the table for multiple keywords. I've tried several examples I found by searching SQL forums yet I keep getting Incorrect syntax near 'keyword*'. Does CF do it differently?

 

Something Like this....

<cfif isdefined("form.search1")>

<cfquery name="Messages" datasource="somedatasource">
Select *
From SomeTable
Where AffiliateID = #Session.AffiliateID# AND Contains(messagefile, '#Form.Search1#*')
<cfif isdefined("form.search2")>

AND Contains(messagefile, '#Form.Search2#*')

</cfif>

<cfif isdefined("form.search3")>

AND Contains(messagefile, '#Form.Search3#*')

</cfif>

Order by SomeField
</cfquery>

</cfif>

 

Greatly appreciate any help...

975
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 12, 2019 Sep 12, 2019

Basically you've already answered your question, but it's not Contains() that goes in the WHERE clause.

 

Also, I would advise to not use SELECT *; enter all the column names that you want to access.  This will be much more efficient.  SELECT * forces the database to reference a system table to get the column names; and unless you really do need every column, it's fetching data that takes up bandwidth that you're going to just throw away.

 

Depending upon what flavour of SQL you're using, the WHERE clause should be something like:

 

WHERE affiliateID = <cfqueryparam value="#session.affiliateID#" />

     AND messagefile = <cfqueryparam value="#form.search1" />

<cfif StructKeyExists(form,"search2")>

    AND messagefile = <cfqueryparam value="#form.search2#" />

</cfif>

 

HTH,

 

^ _ ^

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 12, 2019 Sep 12, 2019

I replied to this message a while ago, and my suggestion is not showing.  WTF??

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 12, 2019 Sep 12, 2019

I replied to your question, but it seems to have disappeared.

 

Depending upon what flavour of SQL you are using, the where clause should be:

WHERE messagefile = <cfqueryparam value="#form.search1#" />

This is assuming that 'messagefile' is a text or integer datatype, and not a BLOB/CLOB as the 'file' part of the name suggests.

 

HTH,

 

^ _ ^

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Sep 12, 2019 Sep 12, 2019
Thanks for the reply. What I am trying to accomplish is the equivalent of multiple likes. For example the messagefile is "The Quick Brown Fox Jumped Over the Candle.mp3" and the user to be able to search keywords "quick" "fox" "Candle" and it match. I know how to match on 1 keyword ... Where messagefile LIKE '%#Form.Search1#%' but not multi words.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 13, 2019 Sep 13, 2019
Since the messagefile is a BLOB/CLOB, it _can't_ look into the file for the filename (assuming that's what the search is for.) You'd have to get the filename separately and insert that into the database along with the file, and search that column. V/r, ^ _ ^
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Sep 13, 2019 Sep 13, 2019
The field MessageFile is not a BLOB/CLOB is its nvarchar.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Sep 13, 2019 Sep 13, 2019
Wow... I am not a fan of this new forum format..... Here's the header and 1 row from the database for example. ID MessageUID MessageFile AffiliateID SerialGroup Size LastUsed 3331 7kw21XGy ouac_01_Selling Clothes music.mp3 3 M1622438/20181002 262477 2019-04-08
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 12, 2019 Sep 12, 2019

This is really not a coldfusion problem. Indeed, it's not one well served by "normal sql". Instead, you want to look to your database (sql server? mysql? oracle?) for any provision it may have for "full text search" or the like. That would allow you to more easily "find any word or phrase" within some column or columns of data. Then once you find how that DB supports that feature, THEN (if it's based on SQL, or a stored procedure call) you can put it into CF to process it.

 

Hope that's helpful. If not, let us know what more you may need.


/Charlie (troubleshooter, carehart. org)
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Sep 12, 2019 Sep 12, 2019
Hi Charlie. The reason I asked here was to find out of CF did it differently. I found an example on a Microsoft SQL forum that said you use Contains(messagefile, '#Form.Search1#*') but that throws an error on CF.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 12, 2019 Sep 12, 2019
Sorry, I was a bit distracted when I was writing. Also getting used to these new forums. So about the contains not working, here's what I would propose: take a fully working example (that you show working in SSMS) and drop it into a cfquery--no variables, no tags. Just see if it works. If it does, then whatever was wrong for you is how those tags and variables are evaluating. If the plain sql does not work, that would be a different problem. Let us know. Sorry, have to run to a meeting so can't even test this myself for you at the moment.

/Charlie (troubleshooter, carehart. org)
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 16, 2019 Sep 16, 2019

AH! Okay.. it _IS_ the filename. (BTW.. you should replace all spaces in filenames with either underscore or %20.)

 

 

<cfquery name="Messages" datasource="somedatasource">
Select ID, MessageUID, MessageFile, AffiliateID, SerialGroup, Size, LastUsed
From SomeTable
Where AffiliateID = <cfqueryparam value="#Session.AffiliateID#" />
     AND messagefile LIKE <cfqueryparam value="%#trim(form.search1)#%" />
<cfif isdefined("form.search2")>
     AND messagefile LIKE <cfqueryparam value="%#trim(form.search2)#%" />
</cfif>
<cfif isdefined("form.search3")>
     AND messagefile LIKE <cfqueryparam value="%#trim(form.search3)#%" />
</cfif>
Order by SomeField
</cfquery>

 

 

HTH,

 

^ _ ^

 

UPDATE:  If you are using CF10 or later, be sure to pass all search keywords through canonicalize() to prevent SQL injection.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Sep 20, 2019 Sep 20, 2019
Thanks - that did it - and the real names don't have spaces. Not a fan of not being able to mark this as the correct answer in this new forum.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 21, 2019 Sep 21, 2019
As I view the UI for the forums, on the web (on a desktop computer, in Chrime), I do see a "correct answer" option beneath each reply.

/Charlie (troubleshooter, carehart. org)
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 23, 2019 Sep 23, 2019
LATEST
Glad to hear that it's working. Yeah, I'm not a fan of the "portal", at all. Wishing Jive were back in place.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources