Solved

Is there a SQL Server equivalent to the format(X,Y) in MySQL?

Posted on 2007-03-21
6
211 Views
Last Modified: 2008-01-09
Is there an equivalent function in SQL Server to the format(X,Y) in MySQL?

I want to display a numeric value with its comma separators.

Many Thanks
0
Comment
Question by:inghfs
  • 3
  • 3
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18764712
yes and no.
you can try to use the convert function:
select convert(money, yourfield) from yourtable

however, the convert function is far from the flexibility you are used to.
you should write your own format function, eventually based on the results of convert() function.
0
 

Author Comment

by:inghfs
ID: 18764748
Do you have a similar pre-written function which would be amended; I'm looking into formatting the field from 1000 to 1,000.

Thanks
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18764811
you can check out this code:
select replace(convert(varchar(10),convert(smallmoney, 1000),3), ".00", "")
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:inghfs
ID: 18764906
Sorry, I was just using 1000 to 1,000 as an example. How would I do this for an arbitrary number. Similar to doing FORMAT(field, '#,###,###,###') in MySQL
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18764989
as said, with arbitrary format, this is not available.
my suggestion above should do your requested format
0
 

Author Comment

by:inghfs
ID: 18764996
Okay, Many Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now