mysql format function

[K.]

Hello!

I have a number 30000.
I want to format it into 30.000.
Mysql format(X,dec) function (format(30000,0)) formats me this number into
30,000, but I
would like to have another seperator ".", so I want to have 30.000 as a
result.

Is it possible to do it in mysql?

Thank you for help
Marcin from Poland

[Peter H. Coffin]

On Mon, 10 Sep 2007 10:21:26 +0200, K. wrote:
> Hello!
>
> I have a number 30000.
> I want to format it into 30.000.
> Mysql format(X,dec) function (format(30000,0)) formats me this number into
> 30,000, but I
> would like to have another seperator ".", so I want to have 30.000 as a
> result.
>
> Is it possible to do it in mysql?

There's a complete way and an incomplete way to do this:

The complete way would be to petition the developers to add support for
the numeric locale facet in addition to ctype and collate. While I'm
fairly confident that this would be soundly endorsed as a fine idea,
and probably already on the list of things to do, it may take some
considerable time before it actually is implemented.

The incomplete way be to instead change the thousands on the fly thusly:

REPLACE(FORMAT(30000,0), ',', '.')

A slightly better way would be to depend on your application to do
whatever conversion you want done. That makes it slightly easier to
replace databasse systems if you decide to.

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord

[Peter H. Coffin]

On Mon, 10 Sep 2007 10:21:26 +0200, K. wrote:
> Hello!
>
> I have a number 30000.
> I want to format it into 30.000.
> Mysql format(X,dec) function (format(30000,0)) formats me this number into
> 30,000, but I
> would like to have another seperator ".", so I want to have 30.000 as a
> result.
>
> Is it possible to do it in mysql?

There's a complete way and an incomplete way to do this:

The complete way would be to petition the developers to add support for
the numeric locale facet in addition to ctype and collate. While I'm
fairly confident that this would be soundly endorsed as a fine idea,
and probably already on the list of things to do, it may take some
considerable time before it actually is implemented.

The incomplete way be to instead change the thousands on the fly thusly:

REPLACE(FORMAT(30000,0), ',', '.')

A slightly better way would be to depend on your application to do
whatever conversion you want done. That makes it slightly easier to
replace databasse systems if you decide to.

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord

[K.]

U¿ytkownik "Peter H. Coffin" <hellsop@ninehells.com> napisa³ w wiadomo¶ci
news:slrnfeagr3.cg8.hellsop@abyss.ninehells.com...
> On Mon, 10 Sep 2007 10:21:26 +0200, K. wrote:
>> Hello!
>>
>> I have a number 30000.
>> I want to format it into 30.000.
>> Mysql format(X,dec) function (format(30000,0)) formats me this number
>> into
>> 30,000, but I
>> would like to have another seperator ".", so I want to have 30.000 as a
>> result.
>>
>> Is it possible to do it in mysql?
>
> There's a complete way and an incomplete way to do this:
>
> The complete way would be to petition the developers to add support for
> the numeric locale facet in addition to ctype and collate. While I'm
> fairly confident that this would be soundly endorsed as a fine idea,
> and probably already on the list of things to do, it may take some
> considerable time before it actually is implemented.
>
> The incomplete way be to instead change the thousands on the fly thusly:
>
> REPLACE(FORMAT(30000,0), ',', '.')
>
> A slightly better way would be to depend on your application to do
> whatever conversion you want done. That makes it slightly easier to
> replace databasse systems if you decide to.
>
> --
> 6. I will not gloat over my enemies' predicament before killing them.
> --Peter Anspach's list of things to do as an Evil Overlord


Thank you for your post.
I have tried before adding your post to newsgroup and I thought up
the same solution. I thought it won`t work because I always have the
such imagination in my mind, that format function in many languages
only formats the number, but the core number is still unchangeable,
so I thought that this replace function will not make any effect. Format
function in many languages only changes number view but not core data.
I hope you know what I mean. It is hard for me to explain it carefully.

Thank you anyway for post
Marcin

[K.]

U¿ytkownik "Peter H. Coffin" <hellsop@ninehells.com> napisa³ w wiadomo¶ci
news:slrnfeagr3.cg8.hellsop@abyss.ninehells.com...
> On Mon, 10 Sep 2007 10:21:26 +0200, K. wrote:
>> Hello!
>>
>> I have a number 30000.
>> I want to format it into 30.000.
>> Mysql format(X,dec) function (format(30000,0)) formats me this number
>> into
>> 30,000, but I
>> would like to have another seperator ".", so I want to have 30.000 as a
>> result.
>>
>> Is it possible to do it in mysql?
>
> There's a complete way and an incomplete way to do this:
>
> The complete way would be to petition the developers to add support for
> the numeric locale facet in addition to ctype and collate. While I'm
> fairly confident that this would be soundly endorsed as a fine idea,
> and probably already on the list of things to do, it may take some
> considerable time before it actually is implemented.
>
> The incomplete way be to instead change the thousands on the fly thusly:
>
> REPLACE(FORMAT(30000,0), ',', '.')
>
> A slightly better way would be to depend on your application to do
> whatever conversion you want done. That makes it slightly easier to
> replace databasse systems if you decide to.
>
> --
> 6. I will not gloat over my enemies' predicament before killing them.
> --Peter Anspach's list of things to do as an Evil Overlord


Thank you for your post.
I have tried before adding your post to newsgroup and I thought up
the same solution. I thought it won`t work because I always have the
such imagination in my mind, that format function in many languages
only formats the number, but the core number is still unchangeable,
so I thought that this replace function will not make any effect. Format
function in many languages only changes number view but not core data.
I hope you know what I mean. It is hard for me to explain it carefully.

Thank you anyway for post
Marcin

[Peter H. Coffin]

On Fri, 14 Sep 2007 13:49:55 +0200, K. wrote:
>
> U¿ytkownik "Peter H. Coffin" <hellsop@ninehells.com> napisa³ w wiadomo¶ci
> news:slrnfeagr3.cg8.hellsop@abyss.ninehells.com...
>> On Mon, 10 Sep 2007 10:21:26 +0200, K. wrote:
>>> Hello!
>>>
>>> I have a number 30000.
>>> I want to format it into 30.000.
>>> Mysql format(X,dec) function (format(30000,0)) formats me this number
>>> into
>>> 30,000, but I
>>> would like to have another seperator ".", so I want to have 30.000 as a
>>> result.
>>>
>>> Is it possible to do it in mysql?
>>
>> There's a complete way and an incomplete way to do this:
>>
>> The complete way would be to petition the developers to add support for
>> the numeric locale facet in addition to ctype and collate. While I'm
>> fairly confident that this would be soundly endorsed as a fine idea,
>> and probably already on the list of things to do, it may take some
>> considerable time before it actually is implemented.
>>
>> The incomplete way be to instead change the thousands on the fly thusly:
>>
>> REPLACE(FORMAT(30000,0), ',', '.')
>>
>> A slightly better way would be to depend on your application to do
>> whatever conversion you want done. That makes it slightly easier to
>> replace databasse systems if you decide to.
>>
>> --
>> 6. I will not gloat over my enemies' predicament before killing them.
>> --Peter Anspach's list of things to do as an Evil Overlord
>
>
> Thank you for your post.
> I have tried before adding your post to newsgroup and I thought up
> the same solution. I thought it won`t work because I always have the
> such imagination in my mind, that format function in many languages
> only formats the number, but the core number is still unchangeable,
> so I thought that this replace function will not make any effect. Format
> function in many languages only changes number view but not core data.
> I hope you know what I mean. It is hard for me to explain it carefully.

Nope, you explained well enough. Then what you would probably want to do
is depend on your application language to manage the formatting then.
This would allow the database to remain independant, and it becomes much
easier to re-localize the application for another formatting standard.
You won't have to rewrite the queries then, and can depend on the locale
features of the operating sustem on the client, if any.

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord

[Peter H. Coffin]

On Fri, 14 Sep 2007 13:49:55 +0200, K. wrote:
>
> U¿ytkownik "Peter H. Coffin" <hellsop@ninehells.com> napisa³ w wiadomo¶ci
> news:slrnfeagr3.cg8.hellsop@abyss.ninehells.com...
>> On Mon, 10 Sep 2007 10:21:26 +0200, K. wrote:
>>> Hello!
>>>
>>> I have a number 30000.
>>> I want to format it into 30.000.
>>> Mysql format(X,dec) function (format(30000,0)) formats me this number
>>> into
>>> 30,000, but I
>>> would like to have another seperator ".", so I want to have 30.000 as a
>>> result.
>>>
>>> Is it possible to do it in mysql?
>>
>> There's a complete way and an incomplete way to do this:
>>
>> The complete way would be to petition the developers to add support for
>> the numeric locale facet in addition to ctype and collate. While I'm
>> fairly confident that this would be soundly endorsed as a fine idea,
>> and probably already on the list of things to do, it may take some
>> considerable time before it actually is implemented.
>>
>> The incomplete way be to instead change the thousands on the fly thusly:
>>
>> REPLACE(FORMAT(30000,0), ',', '.')
>>
>> A slightly better way would be to depend on your application to do
>> whatever conversion you want done. That makes it slightly easier to
>> replace databasse systems if you decide to.
>>
>> --
>> 6. I will not gloat over my enemies' predicament before killing them.
>> --Peter Anspach's list of things to do as an Evil Overlord
>
>
> Thank you for your post.
> I have tried before adding your post to newsgroup and I thought up
> the same solution. I thought it won`t work because I always have the
> such imagination in my mind, that format function in many languages
> only formats the number, but the core number is still unchangeable,
> so I thought that this replace function will not make any effect. Format
> function in many languages only changes number view but not core data.
> I hope you know what I mean. It is hard for me to explain it carefully.

Nope, you explained well enough. Then what you would probably want to do
is depend on your application language to manage the formatting then.
This would allow the database to remain independant, and it becomes much
easier to re-localize the application for another formatting standard.
You won't have to rewrite the queries then, and can depend on the locale
features of the operating sustem on the client, if any.

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord