Message-ID: <4BA83A3C.5080602@hogranch.com>
Date: Mon, 22 Mar 2010 20:49:16 -0700
From: John R Pierce <pierce@hogranch.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: Re: string functions and operators
References: <22285.35517.qm@web113713.mail.gq1.yahoo.com>
In-Reply-To: <22285.35517.qm@web113713.mail.gq1.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Neil Stlyz wrote:
> Hello,
>  
> I have a dilema and I was hoping someone here may offer guidance or 
> assistance. I bet this is a very simple question for someone out there 
> but I am having problems coming up with a solution. Here it is...
>  
> suppose I have a field with the following values:
>  
> 77.1
> 77.2
> 134.1
> 134.2
> 134.3
> 5.1
> 5.2
>  
> I need two seperate SELECT queries. One would return the following 
> values (everything left of the decimal point)
>  
> 77
> 77
> 134
> 134
> 5
> 5
>  
> The second query would return all of the values to the right of the 
> decimal point:
>  
> 1
> 2
> 1
> 2
> 3
> 1
> 2

silly me says..

    SELECT FLOOR(x), x-FLOOR(x) FROM TABLE;

at least for numeric values.

but, in string space, ummmm...

    select split_part(x,'.',1), split_part(x,'.',2) from table;

or
    select regexp_replace(x, '\.[0-9]*$',''), 
regexp_replace(x,'^[0-9]*\.','') from table;

or god knows how many others.