Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    7

    Unanswered: regular expression matching, but in reverse.

    Hi everyone,

    ok, so I know how to use RLIKE to match regular expressions in mysql. So let's say I have an isbn number of:

    0-06-430022-6

    but someone wants to search for it by entering:

    0064300226

    If it were the other way around we are kosher, but how do you search in this direction? Meaning, I would have to grab the correct isbn number and remove the dashes for every isbn in my database and match it against the search string. Any ideas? Maybe someone can point me in the right direction?

    Thank you anyone who thinks about this

  2. #2
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    Quote Originally Posted by kukusz
    <snip snip>
    If it were the other way around we are kosher, but how do you search in this direction? Meaning, I would have to grab the correct isbn number and remove the dashes for every isbn in my database and match it against the search string. Any ideas? Maybe someone can point me in the right direction?

    Thank you anyone who thinks about this
    Wouldn't it be easier to store the ISBNs in a format like 0130926418?
    You have a function formatISBN() that takes 0130926418 and returns 0-13-092641-8, used when you want to print the numbers in a pretty format. When doing comparisions and lookups you simply use the "raw" format without any dashes. When accepting input from search forms you simply strip away all characters that is not 0-9.

    Well, that's how I would have done =)

    If you really want to store the numbers in the other format, why not simply format the incoming (accepted from input form/terminal or where ever input comes into your application) data into a "pretty" ISBN before doing the lookup? That is, doing a lookup on formatISBN("0130926418") (0-13-092641-8).

  3. #3
    Join Date
    Jun 2004
    Posts
    7
    Actually, that is a great idea, but I _think_ that isbn dashes can occur anywhere following some algorithm. There is a whole isbn validator that was written into the code to make sure that the entered isbn was kosher, with the dashes and stuff written in the right place. The last number, following the last dash, is actually a checksum value for all the other numbers. So, if there is a stringent '-' location then this will work fine, but unfortunately I don't think we know where the dashes are.

  4. #4
    Join Date
    Jun 2004
    Posts
    7
    the solution is:

    SELECT isbn FROM some_table WHERE REPLACE(isbn, '-', '') = '0064300226';

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •