List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 20 2000 7:24pm
Subject:Re: Compare without whitespaces
View as plain text  
In the last episode (Nov 20), Roland Carlsson said:
> Does there exists a way to search a column (char)  of its text
> without white-spaces.  We have a column with technical names and
> there is a lot of "x-54 D" looking strings in that. To make give the
> user a chance of find the things he wants we would to remove
> whitespaces and possibly dashes (-) and slashes (/) when searching.

You could write a UDF that returned a stripped version of whatever
string you passed it.
> I'll guess that i could store the name twice, full and stripped but
> if there exists a good way in mysql to do just this i'm interested.

This would work also, and depending on what you're going to be doing
with the field, it might be worth it.  You just have to decide whether
you'd rather strip the field every time you search on it (by using the
UDF), or just precompute the stripped value and cache it in another

Oracle gets around problems like this by having something called a
function-based index, where you can build an index on any expression,
not just a plain field.  But it can get hairy trying to optimize a
query using them.

	Dan Nelson
Can't use "=" on a primary keykathleen.hanney20 Nov
  • Re: Can't use "=" on a primary keySinisa Milivojevic20 Nov
  • Re: Can't use "=" on a primary keyJeremy D. Zawodny20 Nov
  • Compare without whitespacesRoland Carlsson20 Nov
    • Re: Compare without whitespacesDan Nelson20 Nov