Using "like" operator in MySQL query including a ColdFusion variable

Question

I am building a "related resources" page that searches our library for papers having to do with a specific aircraft. If a visitor is researching the XV-1, I want to find all papers containing the designation "XV-1" in the title. But I don't want to find any papers on the XV-15. The aircraft designation is a ColdFusion variable, and we are using MySQL.

What I am using now:

WHERE title LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#trim(aircraftDesignation)#%" />

This returns "The XV-1's Rotor System" as expected, but also returns "Using RotCFD to Predict Isolated XV-15 Rotor Performance" which we don't want.

I think that if I could query on the aircraft designation, without allowing for any numeric characters immediately afterwards, but allowing for other characters such as apostrophe or comma, that might work. However, I tried

'%#trim(aircraftDesignation)#[^0-9]%'

and

'%#trim(aircraftDesignation)#[!0-9]%'

neither of which return anything at all. I would greatly appreciate any assistance or nudges in the right direction, if this is at all possible! Thank you very much for your advice.

UPDATE: I have done a poor job explaining. My apologies! We do have a field for Aircraft Designation. The related resources query runs only when a visitor lands on a specific page. For example, if he or she lands on the XV-1 page, Aircraft Designation will have be assigned a value of "XV-1." The query for related resources happens automatically based on that assigned Aircraft Designation (different per page). No user input is taken.

My effort to not include numbers after "XV-1" was an attempt to prevent papers on the XV-15 from showing on the XV-1 page. Papers on the XV-15 would show on the XV-15 page, since "XV-15" is the Aircraft Designation. But papers on the XV-167 (if there was such a thing) would not show on the XV-15 page.

I tagged this as ColdFusion because Aircraft Designation is a CF variable being used in the query. My attempts to use SQL (and my research into using REGEXP are either failing because I seem to be not using the variable correctly. Or in the case of REGEXP, unable to use a variable in conjunction with a regular expression.

I hope this helps clarify! Thank you all so much for your help.

ANOTHER UPDATE: Using REGEXP like this:

WHERE title REGEXP '#trim(aircraftDesignation)#[^0-9]'

is working fine so far! A paper with "XV-1" in the title, plus the "XV-1's" paper, are both showing. But the XV-15 papers are not. All of the other searches I have spot-checked seem to be working as expected.

The full text searching is a great option though and I will definitely look into it further. Thanks everybody for your help and suggestions! I really appreciate it.

Answer 1

I would like to thank beloitdavisja for pointing me in the right direction with his comment above:

"You might want to try using MySql's REGEX operator instead of LIKE. dev.mysql.com/doc/refman/5.7/en/regexp.html – beloitdavisja"

This gave me the results I was looking for, plus a lot of other useful info. Thanks! Per Leigh's point, here is the query I ended up using (cfqueryparam omitted for brevity):

SELECT sku, title, content, fileName
FROM sd_productsearch
WHERE title REGEXP '#trim(aircraftDesignation)#[^0-9]' AND sku NOT LIKE 'v_%' AND status = '1'
ORDER BY title

A little explanation: this query only pulls our technical papers, not our general interest magazine articles (SKU numbers starting with "v_"). It also only shows papers active on our library (status of 1). The REGEXP I used allows for a search of the specific Aircraft Designation for that aircraft's page, but filters out any numbers afterwards. It's similar to what I attempted to do initially, but I was unaware of the REGEXP operator. So thanks again beloitdavisja!

UPDATE: thanks for the reminders, Leigh, of scoping variables and (especially) cfqueryparam. If anybody is wondering how to use it with REGEXP, this worked for me:

WHERE title REGEXP <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(aircraftDesignation)#[^0-9]">