An Interesting Problem

[joepeacock]

I have a client with a searchable database of products. There are a little over a million products in the database, and the search is by part number only. Here is the problem. The client wants to be able to have zero and "o" interchangeable in the search. So, if a customer searches for "A031o", it will find all parts that match "Ao31o" "A031o" "A0310" or "Ao310". Of course, they don't want to send their product lists with all "o"s replaced with zeros or anything convenient like that, so I need a function that will generate all versions of what the customer entered with zero or "o" in each position. Keep in mind they may enter a search parameter with no zeros or "o"s or with twenty. Any ideas?

 

[joepeacock]

Okay, I built a tag to do this. Just in case anyone ever needs anything like this, here's my code:

<cfset key = replacenocase(attributes.key,&quot;o&quot;,&quot;0&quot;,&quot;all&quot>

<cfset zerocount = 0>
<cfloop from=&quot;1&quot; to=&quot;#len(key)#&quot; index=&quot;pos&quot;>
<cfif mid(key,pos,1) is &quot;0&quot;>
<cfset zerocount = zerocount + 1>
</cfif>
</cfloop>

<cfif zerocount is 0>
<cfset caller.keylist = attributes.key>
<cfexit>
</cfif>

<cfset keycount = 2 ^ zerocount>

<cfloop from=&quot;1&quot; to=&quot;#keycount#&quot; index=&quot;x&quot;>
<cfset &quot;key#x#&quot; = replace(key,&quot;0&quot;,&quot;&quot;,&quot;all&quot>
</cfloop>

<cfset lastzero = 0>
<cfloop from=&quot;1&quot; to=&quot;#zerocount#&quot; index=&quot;zeropos&quot;>
<cfset lastzero = findnocase(&quot;0&quot;,key,lastzero+1)>
<cfset &quot;pos#zeropos#&quot; = lastzero>
</cfloop>

<cfset skipper = 1>
<cfset pos = 1>
<cfloop condition=&quot;skipper LT keycount&quot;>
<cfset zpos = evaluate(&quot;pos#pos#&quot>
<cfset o0 = &quot;0&quot;>

<cfset count = 0>
<cfloop from=&quot;1&quot; to=&quot;#keycount#&quot; index=&quot;x&quot;>
<cfset &quot;key#x#&quot; = insert(&quot;#o0#&quot;,evaluate(&quot;key#x#&quot,zpos-1)>
<cfset count = count + 1>
<cfif count is skipper>
<cfif o0 is &quot;0&quot;><cfset o0 = &quot;o&quot;><cfelse><cfset o0 = &quot;0&quot;></cfif>
<cfset count = 0>
</cfif>

</cfloop>

<cfset skipper = skipper * 2>
<cfset pos = pos + 1>
</cfloop>

<cfset keylist = &quot;&quot;>
<cfloop from=&quot;1&quot; to=&quot;#keycount#&quot; index=&quot;x&quot;>
<cfset keylist = listappend(keylist,evaluate(&quot;key#x#&quot)>
</cfloop>

<cfset caller.keylist = keylist>


That's it. It works pretty well.

 

[csteinhilber]

That sounds pretty ridiculous. If they can't decide whether their part numbers use &quot;o&quot;'s or zeros, they have bigger problems than whether their customers can find their products or not ;-)

But, since you're just replacing those two characters, you can't really do something like WHERE part_num LIKE 'A%31%'... since that would return A0310, AB31C, A6315, etc.

And, unfortunately, I don't know of any databases that allow you to use sets in it's pattern matching:

   WHERE part_num LIKE 'A[o|0]31[o|0]'

wouldn't that be convenient. Maybe someone else knows how to do that.

But, you could certainly build your SQL statement dynamically, adding WHERE clauses as appropriate:

<CFSET sRevisedPartNum = ReplaceNoCase(sPartNum,&quot;o&quot;,&quot;0&quot;,&quot;ALL&quot;)>

<CFSET nFoundChar = Find(&quot;0&quot;,sRevisedPartNum)>

<CFQUERY ...>
SELECT *
FROM tablename
WHERE part_num = '#sRevisedPartNum#'

<CFLOOP condition=&quot;nFoundChar GT 0&quot;>
	<CFSET sRevisedPartNum = Replace(sRevisedPartNum, &quot;0&quot;,&quot;o&quot;,&quot;ONE&quot;)>
	OR part_num = '#sRevisedPartNum#'
	<CFSET nFoundChar = Find(&quot;0&quot;,sRevisedPartNum)>
</CFLOOP>

</CFQUERY>

or something like that.



-Carl

 

[joepeacock]

Well, it's not my client's problem with the part numbers. They stock parts from hundreds of manufacturers, so they don't really have any say over the part number.

I was working on something about like what you have here, but I realized in a string like &quot;0000&quot; would only be translated to :
&quot;0000&quot;
&quot;o000&quot;
&quot;oo00&quot;
&quot;ooo0&quot; and
&quot;oooo&quot;
Missing things like &quot;0o0o&quot; and &quot;00o0&quot;. That's how I wound up with all of the nested loops above.

 

[csteinhilber]

Whoops... I'd go with Joe's solution.

Mine will catch &quot;A0310&quot;, &quot;Ao310&quot;, and &quot;Ao31o&quot;... but not &quot;A031o&quot;.


-Carl