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

SQL query with multiple contains question.

Contributor ,
Sep 12, 2019 Sep 12, 2019

Copy link to clipboard

Copied

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...

Views

575

Translate

Translate

Report

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

Copy link to clipboard

Copied

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,

 

^ _ ^

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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,

 

^ _ ^

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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, ^ _ ^

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

The field MessageFile is not a BLOB/CLOB is its nvarchar.

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

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
Documentation