Removing Spaces in my search

alfoxy

Hi,

My database contains some columns's that have spaces in them and some that dont.
E.g.
NU 320
NU 311
NU321
NU 321

I want my search to return ones that have a space in them.

E.g. When I search for %NU3% I will get all the results listed above instead of jsut the 3rd one.

Any help would be greatly appreciated.

Thanks

Piranha

If I understand you right it is easy: '%NU 3%'

Edit: If you want it to return both then use OR: SELECT id FROM table WHERE column LIKE '%NU3%' OR column LIKE '%NU 3%'

alfoxy

No I want my users to be able to search without knowing which ones have spaces.

It will be fed in from a form. I cant change all the enrtys in the database because it links to other things that need the spaces.

So if they enter NU3 as a search it will return NU 312, NU322, NU 313 etc.

Piranha

Then you have a problem. The big problem is that you use something as FK that is used elsewhere, otherwise it would have been rather simple to change in the database.

But I'm not wrong. You just need to find out if the user have placed a space or not, then make sure that there is one version with a space and one without. It have nothing to do with the database search in itself, it's done best in PHP.

One idea is to use [man]substr[/man] and [man]is_numeric[/man] to find the first number, then use [man]substr_replace[/man] to add or remove the space in the second alternative.

Edit: Another way to solve it is to create a new column in the database where you have all the codes in the same fashion. Then you can do search against that column instead.

Roger_Ramjet

You do this with the mysql REPLACE string function

$sql = "SELECT col FROM table WHERE REPLACE(col, ' ', '') LIKE 'NU3%'";