mysql> select * from t2; +----------+ | isbn | +----------+ | 12345 | | 123-45 | | 123-4-5 | | 123-4-56 | | 123-4-57 | | 123-4-58 | | 123-3-58 | | 123-3-58 | +----------+ 8 rows in set (0.00 sec)
mysql> select * from t2 where replace(isbn,'-','')=replace('1-2-3-4-5','-','');
+---------+
| isbn |
+---------+
| 12345 |
| 123-45 |
| 123-4-5 |
+---------+
3 rows in set (0.00 sec)
mysql>
Note that in my example, I had three entries with essentially the same isbn number, just formatted differently. This is basically the same idea as before except now we are replacing the '-' in the data in both the table and the user input string.
This seemed simpler than trying to first strip all of the '-' from the user string and then re-insert them in the right places. You can do this, but it's a lot clunkier and I wouldn't recommend it. If you are curious, the sql statement is:
mysql> select * from t2 where isbn=
-> concat(
-> substring(
-> replace('1-2-3-4-5','-',''),
-> 1,
-> 3),
-> '-',
-> substring(
-> replace('1-2-3-4-5','-',''),
-> 4,
-> 5)
-> );
+--------+
| isbn |
+--------+
| 123-45 |
+--------+
1 row in set (0.01 sec)What I did here as to first strip all of the '-' out of the user string, since we weren't sure where or if they were there. Then I rebuilt the string to match the pattern ###-## using concat and substring. As I said, this just seems far too clunky to deal with even if it does work.
Bob
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
