Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2005
    Posts
    9

    Unanswered: [Match any Portion] - Possible in Mysql?

    Hello,

    We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.

    Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.

    Any ideas? or are we stuck with using MSSQL. We know this will work with MSSQL.

    Thank you very much. This is a huge problem for us.

    Jim

  2. #2
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    Is that a text field or a number field?

  3. #3
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    try dis,
    select * from tablename where cast(floatfield as char) like '60%'

  4. #4
    Join Date
    Mar 2005
    Posts
    9
    Hi there,

    Hi there,

    Here you can see an example of what I am talking about.

    First, go to this website http://www.bell-electrical.com. On the left side there is an "Inventory Search" section. Enter Part no. 4984.600 ( You don't have to enter a manufacturer, or check any radio boxes. ).

    The website will now redirect to another website and display your result. It will contain the part number 4984600 without the decimal. That particular website is using an ASP script with an MSSQL backend. This is why I say it works with MSSQL. Whether it is the ASP script or something else using sql, I am not exactly sure, but they are doing something to make this work.

    I am basically looking to implement something like this using php and mysql, but am unsure if it's possible. I have noticed that most big php scripts including vbulletin for example don't have this function either.

    Any ideas on how they might be achieving this would be great.

    Anyway, hopefully this reply will help everyone understand what I am trying to accomplish.

    Thanks agains

  5. #5
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    Will u pls put a sample data ? And what is the fieldtype?

  6. #6
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by ricoche
    Here you can see an example of what I am talking about.

    First, go to this website http://www.bell-electrical.com. On the left side there is an "Inventory Search" section. Enter Part no. 4984.600 ( You don't have to enter a manufacturer, or check any radio boxes. ).

    The website will now redirect to another website and display your result. It will contain the part number 4984600 without the decimal. That particular website is using an ASP script with an MSSQL backend. This is why I say it works with MSSQL. Whether it is the ASP script or something else using sql, I am not exactly sure, but they are doing something to make this work.
    Aren't you jumping to a conclusion here?
    Perhaps they are just removing punctuation from your input because they also store part numbers without punctuation.
    If you e.g. put in '4984A600', it will not work (not even when you check 'Match any portion')

    --
    felix

  7. #7
    Join Date
    Mar 2005
    Posts
    9
    Aren't you jumping to a conclusion here?
    Perhaps they are just removing punctuation from your input because they also store part numbers without punctuation.
    If you e.g. put in '4984A600', it will not work (not even when you check 'Match any portion')
    Hi Felix,

    I think you are on to something. They probably are removing the punctuation. Would this be something I put in a word noise file? Or do you think they did this using their asp script?

    Thanks for the very helpful tip.

    Jim

  8. #8
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by ricoche
    They probably are removing the punctuation. Would this be something I put in a word noise file? Or do you think they did this using their asp script?
    You can add an additional (unique) column in your parts table in which you store your part number with a predefined set of characters removed (e.g. '.,- ').
    In MySQL you could do this with

    Code:
    ALTER TABLE parts ADD COLUMN cpartnr CHAR(20) NOT NULL DEFAULT '';
    UPDATE parts SET cpartnr = REPLACE(REPLACE(REPLACE(REPLACE(partnr, '.', ''), ',', ''), '-', ''), ' ', '');
    ALTER TABLE parts ADD UNIQUE (cpartnr);
    Then make sure you remove the same characters from the user's input string before you do the SELECT.

    --
    felix

  9. #9
    Join Date
    Mar 2005
    Posts
    9
    Hi felix,

    You are brilliant!

    Your answers directed me exactly to my problem and the solution. Your SQL helped me to create exactly what I needed in the database. Plus I was able to find the modification needed for the php to strip the period or any other punctuation if needed. The combination of the two have solved all of my current issues.

    Thanks so much for understanding my post and pointing me in the right direction.

    - Jim

Posting Permissions

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