Thread: simple SQL question

simple SQL question

From

Joshua

Date:

25 June 2007, 14:29:46

I have a column with the following values (example below)

5673
4731
4462
5422
756
3060

I want the column to display the numbers as follows:

56.73
47.31
44.62
54.22
7.56
30.60

I have been playing around with string functions but cannot seem to 
figure out a quick solution. Does anyone have any suggestions?

Please let me know.

Thanks.

Re: simple SQL question

From

"Rodrigo De León"

Date:

25 June 2007, 14:35:37

On 6/25/07, Joshua <joshua@joshuaneil.com> wrote:
> I have a column with the following values (example below)
>
> 5673
> 4731
> 4462
> 5422
> 756
> 3060
>
> I want the column to display the numbers as follows:
>
> 56.73
> 47.31
> 44.62
> 54.22
> 7.56
> 30.60
>
> I have been playing around with string functions but cannot seem to
> figure out a quick solution. Does anyone have any suggestions?
>
> Please let me know.
>
> Thanks.

Divide by 100.

Re: simple SQL question

From

"Milen A. Radev"

Date:

25 June 2007, 14:54:12

Joshua написа:
> I have a column with the following values (example below)
> 
> 5673
> 4731
> 4462
> 5422
> 756
> 3060
> 
> I want the column to display the numbers as follows:
> 
> 56.73
> 47.31
> 44.62
> 54.22
> 7.56
> 30.60
> 
> I have been playing around with string functions but cannot seem to
> figure out a quick solution. Does anyone have any suggestions?


Use "to_char(int, text)", details here -
http://www.postgresql.org/docs/current/static/functions-formatting.html


-- 
Milen A. Radev

Re: simple SQL question

From

manchicken

Date:

25 June 2007, 15:11:01

On Monday 25 June 2007 09:38:39 Milen A. Radev wrote:
> Joshua написа:
> > I have a column with the following values (example below)
> >
> > 5673
> > 4731
> > 4462
> > 5422
> > 756
> > 3060
> >
> > I want the column to display the numbers as follows:
> >
> > 56.73
> > 47.31
> > 44.62
> > 54.22
> > 7.56
> > 30.60
> >
> > I have been playing around with string functions but cannot seem to
> > figure out a quick solution. Does anyone have any suggestions?
>
> Use "to_char(int, text)", details here -
> http://www.postgresql.org/docs/current/static/functions-formatting.html

Don't use to_char unless you actually want character data though.

Just try this...

SELECT (column/100) FROM table;

--
~ manchicken <><
(A)bort, (R)etry, (I)nfluence with large hammer.
09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0


Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

Re: simple SQL question

From

"Fernando Hevia"

Date:

25 June 2007, 15:19:59

> > I have a column with the following values (example below)
> >
> > 5673
> > 4731
> > 4462
> > 5422
> > 756
> > 3060
> >
> > I want the column to display the numbers as follows:
> >
> > 56.73
> > 47.31
> > 44.62
> > 54.22
> > 7.56
> > 30.60
> >
> > I have been playing around with string functions but cannot seem to
> > figure out a quick solution. Does anyone have any suggestions?
>
> Don't use to_char unless you actually want character data though.
> 
> Just try this...
> 
> SELECT (column/100) FROM table;

Cast the column in order to get the decimal part:

SELECT (column::real/100) FROM table;

Re: simple SQL question

From

Ireneusz Pluta

Date:

25 June 2007, 15:33:44


Joshua napisał(a):
> I have a column with the following values (example below)
> 
> 5673
> 4731
> 4462
> 5422
> 756
> 3060
> 
> I want the column to display the numbers as follows:
> 
> 56.73
> 47.31
> 44.62
> 54.22
> 7.56
> 30.60
> 
> I have been playing around with string functions but cannot seem to 
> figure out a quick solution. Does anyone have any suggestions?

if I read you correctly, I might suggest:

select round(your_column/100.0, 2) as divided_by_100 from your_table;

(note dividing by 100.0 (decimal), not just by 100 (integer))

> 
> Please let me know.
> 
> Thanks.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>               http://archives.postgresql.org
> 
> 
> 
>

Re: simple SQL question

From

manchicken

Date:

25 June 2007, 17:15:01

On Monday 25 June 2007 10:19:49 Fernando Hevia wrote:
> > > I have a column with the following values (example below)
> > >
> > > 5673
> > > 4731
> > > 4462
> > > 5422
> > > 756
> > > 3060
> > >
> > > I want the column to display the numbers as follows:
> > >
> > > 56.73
> > > 47.31
> > > 44.62
> > > 54.22
> > > 7.56
> > > 30.60
> > >
> > > I have been playing around with string functions but cannot seem to
> > > figure out a quick solution. Does anyone have any suggestions?
> >
> > Don't use to_char unless you actually want character data though.
> >
> > Just try this...
> >
> > SELECT (column/100) FROM table;
>
> Cast the column in order to get the decimal part:
>
> SELECT (column::real/100) FROM table;
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Good point.  Thanks for the catch :)

-- 
~ manchicken <><
(A)bort, (R)etry, (I)nfluence with large hammer.
09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0


Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html