Format numeric to display dollar value $1,000.00

Hi,
Currently, I have a numeric field stored a value of 1000, how can I covert
it to varchar value and display it as $1,000.00
Thanks>> Currently, I have a numeric field stored a value of 1000, how can I cover
t it to varchar value and display it as $1,000.00 <<
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.
You are the kid in the class that the other students make fun of
because he just does not understand ...|||I need to store a string of combine text that should should appear ....
$1,000.00
I'm wondering, if there that's a way to do that.
"--CELKO--" wrote:

> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||The following example illustrates how this can be achieved:
CREATE TABLE dbo.currency
(
col1 INT
)
INSERT dbo.currency SELECT 1000
INSERT dbo.currency SELECT 10
INSERT dbo.currency SELECT 999999
SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
FROM dbo.currency
HTH
- Peter Ward
WARDY IT Solutions
"slimla" wrote:
> I need to store a string of combine text that should should appear ....
> $1,000.00
> I'm wondering, if there that's a way to do that.
>
> "--CELKO--" wrote:
>|||Extract from my blog:
http://sqlblogcasts.com/blogs/tonyr...1/429.aspx....
Introduction
Application programmers and Business Intelligent professionals are faced
with having to format data - taylored into what the users want. We have two
choices as to where we do this processing, keep it in the database using the
facilities of the database engine, for instance T-SQL, standard SQL dialect,
CLR, XML or whatever the product has to offer or we can bring the data out
of the database and down into the front end application or middle tier and
format the data there (keep the database for store and retreive only).
My Opinion
The IT industry is full of rules and best practices unfortunetly some of
these rules and best practices aren't based on current technology or
business problems, in fact some of the rules and best practices are based on
techniques adopted in the 70's and 80's on mainframes or early client server
architecture.
No product is just a database anymore, sure SQL Server stores and retrieves
data but it also offers us a lot more, in fact its moving more towards being
the middle and data tiers in the three tier architecture now that SQL Server
can be a web service and the inclusion of CLR.
Data formatting, be it paging, value concatenation should always been done
where it is most efficient to do it. Consider (and benchmark) where its most
efficient to do this, would you really drag 1 million rows into the middle
tier or client browser only to get page 2 of 20 rows? It doesn't make sense.
Relating this to a well known expert, --CELKO--, he states that you should
NEVER do formatting in the database and it should always be done in the
front end. Think this through, take value concatenation for instance, say
you need to create a list of values for a given product category, for
instance for a given person show the mailing lists they belong to. In the
database this will be held in rows, so if a person belongs to 5 mailing
lists there will be 5 rows, now, say the user requires the values to be
normalised so they are displayed on just one line entry. We have two
choices, drag the 5 rows down to the front end or middle tier and use a 4GL
to process the data or we can use some of the extensions available in SQL
Server to do this.
Example
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select ', ' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 3, 100 )
from mailing_list m1
Gives this result :-
alex r List A
joe r List A, List B
tony r List A, List B, List C
Now, just how easy was that! It only takes a few lines of SQL and you have
also saved a lot of network traffic back out to the middle tier or front
end.
So, my point is this: whatever you do - always think through what you are
doing, don't just follow 'rules' blindly!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150411335.797938.9320@.f6g2000cwb.googlegroups.com...
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||slimla wrote:
> Hi,
> Currently, I have a numeric field stored a value of 1000, how can I covert
> it to varchar value and display it as $1,000.00
> Thanks
you can also set up a compute column to have string instead of integer.
select '$' + cast(<int value> as varchar(20))|||Tony,
Very well said!
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam|||Thanks All
"P. Ward" wrote:
> The following example illustrates how this can be achieved:
> CREATE TABLE dbo.currency
> (
> col1 INT
> )
> INSERT dbo.currency SELECT 1000
> INSERT dbo.currency SELECT 10
> INSERT dbo.currency SELECT 999999
> SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
> FROM dbo.currency
> HTH
> - Peter Ward
> WARDY IT Solutions
>
> "slimla" wrote:
>|||I like that method, sort of gives you a formatting tier within the data
tier - nice and central and it doesn't effect storage, gives you a standard
view for people to use and code against... A lot more easier and
maintainable than using views as well...
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"BurgerKING" <syi916@.gmail.com> wrote in message
news:1150461726.914638.202330@.i40g2000cwc.googlegroups.com...
> slimla wrote:
>
> you can also set up a compute column to have string instead of integer.
> select '$' + cast(<int value> as varchar(20))
>