Topic: MySQL quer results with or without spaces

[Brian]

In my db I have a part number BR 6747.

If I type in the search query, BR 6 or br 67, etc, I get a match. I have some basic pattern matching but if I type:

BR6747 (<- no space) there is no match, in the db the part is BR 6747.

How can I get a match when the user enters without a space but the data in the db has a space?

The opposite is easy enough, but this way is proving to be difficult (for me).

Thanks.

[Brian]

Just to follow up here is more info:

I have "BR 6742" or what ever in the database.

If the user enters BR6742, or BR6 or BR 6 I want all of those to match.

It is easy with REGEX or LIKE adding a % to match:

* user input: BR 6742

to db value:

* db value: BR6742

But if the db has value: BR 6742 and the user enters BR6742 (no space) I am finding it very difficult to understand how to match this format.

Yes, I repeated myself, I am sorry for that.

[James Blond]

maybe you should try to form the user input as you need it. In PHP I'd use preg_split and str_ireplace.

Second thing is you can show an input example over the input box the user sees. Or you have two input boxes like one for the letters and one for the numbers and put it together in your programming.

[Brian]

Yes, I am thinking of splitting this into multiple queries.

Break it up into sections, the numbers isolated, the letters isolated, make an array and then do a "LIKE" or "SOUNDS LIKE" match on each part of the user query. This would be possible.

--
Brian

[James Blond]

I didn't think of splitting the query. I though of something like this