donboe — 2011-03-02T20:18:57-05:00 — #1
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
donboe — 2011-03-03T05:12:36-05:00 — #2
I tried to return results using these wildcards in my query but it doesn't return any results:
P.ref_number LIKE '%#Url.ref_number#%'
P.ref_number LIKE '%#Url.ref_number#'
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
r937 — 2011-03-03T05:49:27-05:00 — #3
how is the value EP273 stored in the table? with a space or without?
donboe — 2011-03-03T05:53:55-05:00 — #4
It is stored as EP 273, with the space!
r937 — 2011-03-03T06:04:57-05:00 — #5
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%'
donboe — 2011-03-03T06:19:33-05:00 — #6
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:
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:
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" )>
ref_number = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( Url.ref_number )#" />
<cfelseif structKeyExists( Url, "product" )>
product_id = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( Url.product )#" />
Excuuus me for using CF code in this post but I didn't know how else to express it
r937 — 2011-03-03T06:47:11-05:00 — #7
yeah, you definitely cannot have two WHERE keywords
why don't you try this ...
WHERE REPLACE(ref_number,' ','') LIKE REPLACE('%#url.ref_number#%',' ','')
donboe — 2011-03-03T06:53:08-05:00 — #8
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:
ref_number LIKE '%EP%'
It returns the right record!
donboe — 2011-03-03T08:12:12-05:00 — #9
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?
guelphdad — 2011-03-03T10:12:58-05:00 — #10
Search terms of less than four characters are ignored.
donboe — 2011-03-03T10:19:08-05:00 — #11
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?
r937 — 2011-03-03T10:26:39-05:00 — #12
only for fulltext matching
he's using LIKE
system — 2011-03-03T10:34:24-05:00 — #13
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.
donboe — 2011-03-03T10:47:21-05:00 — #14
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
guelphdad — 2011-03-03T12:27:22-05:00 — #15
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>