Formatting numbers in SQL

Hi
I need to format the output of a numeric value from SQL-server with a
chosen thousand-separator and decimal-separator.
The Access equivalent would be something like:
Select format(myNumber, "#.###,##") as myFormattedNumber from myTable;
I have searched the net for a solution, and found something about
FORMAT_STRING, but that doesn't seem to be usable in SQL-Server...
Can anyone help me with this?
Regards,
Johnny Nordtvedt, NorwayIn an N-Tier environment it's better to leave presentational features
out of the database and do them in your client/middle tier. Some users
might prefer to configure a different numeric format so it's probably
not something you ought to fix in a SELECT statement.
TSQL does have a CONVERT function, which provides some limited
formatting capability but only if you convert your numerics to a
string. Also, AFAIK the CONVERT function only supports comma as a
thousands separator by default. In reality you should find it much
easier to do the formatting in the client control or form that displays
the data.
David Portas
SQL Server MVP
--|||My problem is that these values is displayed in a Word-document, and the
program that collects these values and inserts them into word, is not
available for formatting, and will not ever be available. So basically, I
am stuck with formatting this in SQL-server.
Regards,
Johnny Nordtvedt, Oslo, Norway|||Is it possible to make a user defined function to do this? And how would I
go by to do this?
Still hope that someone can help me with this. I am certain someone has
done this before?
Regards,
Johnny Nordtvedt, Oslo, Norway|||DECLARE @.numeric NUMERIC(10,2)
SET @.numeric = 123456.78
SELECT
REPLACE(REPLACE(REPLACE(
CONVERT(VARCHAR(10),CAST(@.numeric AS MONEY),1)
,',','~') ,'.',',') ,'~','.')
David Portas
SQL Server MVP
--|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
There are formatting functions on Merge fields in Word. Read the Word
Help file (in English version):
Field Types and Switches
Field Reference
Switches
How to: Highlight merge field, hit Alt-9. Add switches at end of
field:
/# will give numeric format.
/#.#,## I believe will format the number 99999.00 as 99.999,00
Also, read the Word Help articles on Merge Fields.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQlWjTIechKqOuFEgEQLidACfWKC4/ST6Pc1f6yJC3xaIoOO4dC8AoKLM
k5KfIvuctrzjBfvZduWw4W0t
=lpsE
--END PGP SIGNATURE--
Johnny, Norway wrote:
> My problem is that these values is displayed in a Word-document, and the
> program that collects these values and inserts them into word, is not
> available for formatting, and will not ever be available. So basically, I
> am stuck with formatting this in SQL-server.