Solved

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

Posted on 2007-03-21
6
216 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parts and Products table schema in mysql 6 53
INSERT DATE FROM STRING COLUMN 18 56
setup wamp server for first time 2 74
MS Access - need to reduce row size 25 54
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
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 …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 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