Обсуждение: Re: is there a function which elminates spaces?

SELECT trim(trailing ' ' from city_name) AS city_name FROM sys_cities;

You might consider reading the manual as there are a multitude of string
manipulation functions built into postgreSQL....





"Pierre Couderc" <pierre@couderc.ccNOSPAM> wrote in message
news:djidc4$2hgk$1@biggoron.nerim.net...
> 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.

codeWarrior wrote:

>SELECT trim(trailing ' ' from city_name) AS city_name FROM sys_cities;
>
>You might consider reading the manual as there are a multitude of string
>manipulation functions built into postgreSQL....
>
>
You didn't answer his question.  If you're going to rag on someone for
not reading the manual, at least you could read what he's asking.

>"Pierre Couderc" <pierre@couderc.ccNOSPAM> wrote in message
>news:djidc4$2hgk$1@biggoron.nerim.net...
>
>
>>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.

Jeff

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.