Highlighted

SQL query with multiple contains question.

Participant ,
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

408

Likes

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

SQL query with multiple contains question.

Participant ,
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

409

Likes

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
Sep 12, 2019 0
LEGEND ,
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,

 

^ _ ^

Likes

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
Reply
Loading...
Sep 12, 2019 0
LEGEND ,
Sep 12, 2019

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Sep 12, 2019 0
LEGEND ,
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,

 

^ _ ^

Likes

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
Reply
Loading...
Sep 12, 2019 0
Participant ,
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.

Likes

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
Reply
Loading...
Sep 12, 2019 0
LEGEND ,
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, ^ _ ^

Likes

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
Reply
Loading...
Sep 13, 2019 0
Participant ,
Sep 13, 2019

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Sep 13, 2019 0
Participant ,
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

Likes

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
Reply
Loading...
Sep 13, 2019 0
Adobe Community Professional ,
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 (server troubleshooter, carehart.org)

Likes

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
Reply
Loading...
Sep 12, 2019 0
Participant ,
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.

Likes

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
Reply
Loading...
Sep 12, 2019 0
Adobe Community Professional ,
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 (server troubleshooter, carehart.org)

Likes

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
Reply
Loading...
Sep 12, 2019 0
LEGEND ,
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.

Likes

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
Reply
Loading...
Sep 16, 2019 1
Participant ,
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.

Likes

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
Reply
Loading...
Sep 20, 2019 0
Adobe Community Professional ,
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 (server troubleshooter, carehart.org)

Likes

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
Reply
Loading...
Sep 21, 2019 0
LEGEND ,
Sep 23, 2019

Copy link to clipboard

Copied

Glad to hear that it's working. Yeah, I'm not a fan of the "portal", at all. Wishing Jive were back in place.

Likes

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
Reply
Loading...
Sep 23, 2019 0