I have a control panel where the site owner can search for products by product number. It happens quite often though that they make a typo: BAR 2131G instead of BAR2131G (notice the space in the first one. Or instead of the G at the end they type a H (next to the G on the keyboard). I would like to return some alternative records in case something like that occur. What would be the best where clause in such a case? A wildcard LIKE % % or are there any other suggestions?
Thank you in advance
I tried to return results using these wildcards in my query but it doesn’t return any results:
WHERE
(
P.ref_number LIKE '%#Url.ref_number#%'
OR
P.ref_number LIKE '%#Url.ref_number#'
OR
P.ref_number LIKE '#Url.ref_number#%'
)
When I type the correct ref_number (EP 272 in this example) the address bar showes ref_number=EP+272 How should I adjust the query so that it will return results when it was entered as EP272 or EP 273
how is the value EP273 stored in the table? with a space or without?
It is stored as EP 273, with the space!
in that case, your query will find it
by the way, your three conditions with the ORs are redundant
‘%AAA%’ will cover both ‘%AAA’ and ‘AAA%’
But why is it that it doesn’t give me a result. I added the ‘%AAA’ and ‘AAA%’ because I didn’t get any results using the ‘%AAA%’ When I use:
WHERE
ref_number = 'Url.ref_number'
and I use the right ref_number it obviously returns the record I was looking for, but as soon as I replace it in my query with:
WHERE
ref_number LIKE '%Url.ref_number%'
it doesn’t. Can it be, bcause I use two WHERE clauses in the query like this:
<cfif structKeyExists( Url, "ref_number" )>
WHERE
ref_number = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( Url.ref_number )#" />
<cfelseif structKeyExists( Url, "product" )>
WHERE
product_id = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( Url.product )#" />
</cfif>
Excuuus me for using CF code in this post but I didn’t know how else to express it
yeah, you definitely cannot have two WHERE keywords
why don’t you try this …
WHERE REPLACE(ref_number,' ','') LIKE REPLACE('%#url.ref_number#%',' ','')
That seems to work great! Thank you for that. What is this doing Rudy?
Need to edit: This works fine for spaces, but when a typo is made, for example EO instead of EP it generates an error.
Second Edit When I use it hard coded:
WHERE
ref_number LIKE '%EP%'
It returns the right record!
This problem has obviously to do with the space used in ref_number. As example I still used the ref_number as stated before (EP 272) This are the tests so far and the results:
E > Returns result(s)
EP > Returns result(s)
P > Returns result(s)
EP 2 > Returns result(s)
P 2 > Returns result(s)
2 > Returns result(s)
27 > Returns result(s)
72 > Returns result(s)
272 > Returns result(s)
As soon as I enter anything without the space between the P and 2 in my search no result is returned. I start to wonder if I should use a different data type to store this value, whatever that might be?
Search terms of less than four characters are ignored.
Hi guelphdad. Sorry for my ignorance, but how should I read this information? If you see my list above there are many occasions where results are returned with less than four characters?
only for fulltext matching
he’s using LIKE
Probably not a solution best implemented at the DB level. Do you expect your Ref # in a specific format? Maybe you can pre-process the entered value at the Business Logic Layer prior to hitting the DB.
Also, I suggest:
First, trim out spaces and perform other pre-formatting.
Do a count on exact match.
If you get one, return that.
If you get none, do a like-match.
If no results, cut the search term in half, left and right, and search each, unioning the results.
Cheers!
I usually use a pre processed format, but in this case the numbers were already in place and the insisted of keeping them that way. I tried the left indeed with result, but what is bothering me most is the fact that the ref_number without the space isn’t returning any results
Sorry donboe, i had just finished reading a different thread on fulltext matching and then for some reason thought you were using it too. <hangs head>