On Thu, Sep 03, 2009 at 12:12:40PM -0700, sono-io@xxxxxxxxxxxxx wrote:
> Thanks to everyone who has responded. After reading everyone's
> response, I think I have a very simple way to solve my "problem".
>
> Using my original example, if someone wants to find item #
> 4D-2448-7PS, no matter what they type in, I'll take the input, strip
> out all non-alphanumeric characters to make it 4D24487PS, add the
> wildcard character between each of the remaining characters like so,
> 4*D*2*4*4*8*7*P*S, and then do the search.
Your expression, if used to directly search in your SQL table, won't
work. The '*' character isn't a valid wildcard for SQL. In PostgreSQL,
the wildcard for any number of characters is '%', and for a single
character is '_'. I don't know that MySQL understands this same
convention. And who knows about Oracle.
As others have mentioned, it would be ideal (though not very
"normalized") to create a new table column which contains the
alphanumerics without the punctuation characters ('-'). In nearly any
SQL dialect, you could do a simple SELECT using LIKE to find your item,
if you're searching on this extra field.
If you want do the searching in PHP, then it becomes more complicated.
You'll have to strip out the dashes from the user input, and then query
all the keys from your table, and test them using a regular expression.
As mentioned before, this is time-consuming for a large table.
Here's something else to consider: Could there ever be two items which
only differ by the placement of their dashes? Like 4D-2448-7PS versus
4D2-44-87PS? If not, then you should store the item number without
punctuation, and use that as the primary key on your table. Have an
"extra" field which shows the item number with dashes. You can use this
extra field in printing inventory labels or whatever (I don't recall the
context of your original post).
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
