[Match any Portion] - Possible in Mysql?

ricoche · #1 (**permalink**) 10-10-05, 22:09

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

marist89 · #2 (**permalink**) 10-11-05, 10:11

Is that a text field or a number field?

popskie · #3 (**permalink**) 10-11-05, 21:18

try dis,
select * from tablename where cast(floatfield as char) like '60%'

ricoche · #4 (**permalink**) 10-12-05, 03:04

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

popskie · #5 (**permalink**) 10-12-05, 04:01

Will u pls put a sample data ? And what is the fieldtype?

felixg · #6 (**permalink**) 10-12-05, 04:45

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

ricoche · #7 (**permalink**) 10-12-05, 05:16

Quote:

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

felixg · #8 (**permalink**) 10-12-05, 05:30

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

ricoche · #9 (**permalink**) 10-12-05, 08:00

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

Internet Services

Web Development

Digital Marketing

Consumer Tech