Обсуждение: string functions and operators

Поиск
Список
Период
Сортировка

string functions and operators

От
Neil Stlyz
Дата:
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
 
 
Now, I have been using the following information (although very Greek) to try to solve this problem:
 
 
And I have been playing around with the syntax of the following:
 
            substring('112.5' from '%#"___.#"_' for '#')
 
but the aforementioned is not quite working out... can someone please show me a string function that will produce the desired results?
 
Thanks!
~n
 

Re: string functions and operators

От
John R Pierce
Дата:
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.