Re: is there a function which elminates spaces?

On Mon, Oct 24, 2005 at 03:50:33PM -0500, Jeffrey Melloy wrote:
> >>In a WHERE clause, I want to to compare strings ignoring the spaces inside
> >>them. Is therd a function to do that? I do not find it in the
> >>documentation.
> >>
> >>SELECT ... WHERE (ignore_spaces(table.phone_number) ~* igore_spaces(?));
> >>
> >>would be fine but ignore_space() does not exist!
> >>Maybe there is a solution based on regular epxression, but I do not see
> >>it.
> >>
> >>
> I don't see a way to do it through regular expressions, either, though
> in the phone number case, you could split the phone number into
> different columns based on area code, whatever the middle group is
> called, and whatever the last group is called.  Or you could remove the
> spaces before inserting and comparing, or write a function with pl/perl
> or something.  With perl's greater regular expression control, it would
> probably be a one liner.

I'd suggest replace().

test=# select replace('Long spacey string',' ','');
     replace
------------------
 Longspaceystring
(1 row)

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.