Creating a regular expression which would match dashed or non-dashed number would not be that hard
search '00643*' -> '0[-]?0[-]?6[-]?4[-]?4[-]?,*'
but the it would be very expensive in terms of performance if you have a significant number of records. Formatting before the insert, or adding a second column with the stripped version (your original idea) which also gets populated at insert time would be much more efficient. A procedural script/program to convert historical data would also be pretty straightforward.
- michael dykman - [EMAIL PROTECTED]
Luke Majewski wrote:
Hi everyone,
ok, so I know how to use RLIKE to match regular expressions. However, let's say I have an isbn number of:
0-06-430022-6
saved in the database but someone wants to search for it by entering:
0064300226
or even
006-430-0226
So the search query needs to have its dashes removed as do all the isbn values in the database. Removing the dashes from the query is easy, but transforming all the ISBNs on the fly before making the comparison is what I'm hoping to do. I've thought of adding a non-dashed isbn column in the database just for searching purposes but this is a final resort. Oh, and I can't just save the isbn without dashes to begin with or add dashes to the search query because I never know where the dashes are going to be. We have an isbn validator that determines whether the isbn is in a correct format on save but when we search we want to allow people to enter it incorrectly (having the right numbers but dashes can be in the wrong place) and still get results. It would be great if there was some transform I could do on the query result to change all the ISBNs to have their dashes removed. If anyone can point me in the right direction it would be greatly appreciated.
Thanks to anyone who thinks about this
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
