Subsitute of TO_CHAR in transact SQL???

Question

Post reply

Subsitute of TO_CHAR in transact SQL???

KavitaA

Right there with Babe

Points: 794
More actions
April 14, 2008 at 12:17 am

#71550

I wanna format my decimal value say 1234567.66666 to string value $1,234,567.66666
using Transact SQL
How can I do it?
I've tried using cast(value as Money) but it rounds off my value to 2 decimal places but i need 5 decimal places and i also need the formatting with commas and a $ sign in the beginning.
I know it can be done with TO_CHAR and rpad and Column command in Oracle but none of it works in Transact SQL :angry:
Please Help
Thanks,
Kavita

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Oana Schuster SSCommitted Points: 1642 More actions · Answer 1

Hi Kavita,

select '$' + convert(varchar,(convert(money,1234567.666666)),1)

and the result is: $1,234,567.67

That's all i could came up with till now.

Maybe it will help.

Kind regards,

Oana Schuster

KavitaA Right there with Babe Points: 794 More actions · Answer 2

Thanks Oana, I've already tried that but it gives two decimal places only and i want 5.

Oana Schuster SSCommitted Points: 1642 More actions · Answer 3

Hey,

I don't know how to fix that problem 🙁

I am very curios also.. maybe someone will figure it out 😉

Regards,

Oana.

steveb. SSC-Forever Points: 46733 More actions · Answer 4

from BOL

The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent

so you will not be able to go to 5 decimal places with the money data type, have you tried converting to varchar and then breaking up the string using SUBSTRING function?

i will add some code if i get some time today..

KavitaA Right there with Babe Points: 794 More actions · Answer 5

I didn't try breaking it up but i don't see how that will help. I'll wait for you to add code.

Rgds,

Kavita

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

T-SQL is absolutely NOT the place to format currency. This should be done in the GUI so that local currency formatting may be allowed to prevail. Also, you shouldn't even think for a minute that you'll store formatting numbers in a table... basically, it renders them useless for any further calculations.

If you really want to commit suicide using T-SQL as the ends to the mean... SELECT Amount

INTO #MyHead

FROM (

SELECT 1.1 AS Amount UNION ALL

SELECT 12.12 AS Amount UNION ALL

SELECT 123.123 AS Amount UNION ALL

SELECT 1234.1234 AS Amount UNION ALL

SELECT 12345.12345 AS Amount UNION ALL

SELECT 123456.12345 AS Amount UNION ALL

SELECT 1234567.12345 AS Amount UNION ALL

SELECT 12345678.12345 AS Amount UNION ALL

SELECT 123456789.12345 AS Amount)d

SELECT '$'+LTRIM(REPLACE(STUFF(STUFF(RIGHT(SPACE(15)+CAST(Amount AS VARCHAR(16)),16),8,0,','),5,0,','),' ,',' '))

FROM #MyHead

DROP TABLE #MyHead

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 7

I agree with Jeff that SQL isn't the way to do this. Every front end app I've ever worked with will do this better.

If, somehow, you absolutely must do it, Jeff's solution will work with up to 9 digits before the decimal place. (If you're dealing with billions of dollars, add one more Stuff command to it. If you're doing this for the US national debt and need trillions of dollars, add two more. And so on.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

steveb. SSC-Forever Points: 46733 More actions · Answer 8

kavitaasnani (4/14/2008)
I didn't try breaking it up but i don't see how that will help. I'll wait for you to add code.
Rgds,
Kavita

I mean break up the string into parts and concatenate them with a comma between them

But i fully agree with the others, i would never store data formatted like this in the DB, store it as an integer and leave the formatting up to the front end system.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

GSquared (4/14/2008)
I agree with Jeff that SQL isn't the way to do this. Every front end app I've ever worked with will do this better.
If, somehow, you absolutely must do it, Jeff's solution will work with up to 9 digits before the decimal place. (If you're dealing with billions of dollars, add one more Stuff command to it. If you're doing this for the US national debt and need trillions of dollars, add two more. And so on.)

Again - strongly endorsing the "don't do formatting in the database layer", you could always write a CLR function doing the formatting for the occasional time you might need it. You could then for example access .NET's FormatCurrency function that way, which will work no matter how many digits you have.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

I wonder which would work more quickly? I think the CLR might win this one because it uses implicit functions...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Jeff Moden (4/14/2008)
I wonder which would work more quickly? I think the CLR might win this one because it uses implicit functions...

As of right now - "straight T-SQL" wins by a nose, but the two funtions aren't returning the same thing. The comma separators aren't happening, nor is the 5 decimal places, in the STUFF version.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Matt Miller (4/14/2008)
Jeff Moden (4/14/2008)
I wonder which would work more quickly? I think the CLR might win this one because it uses implicit functions...
As of right now - "straight T-SQL" wins by a nose, but the two funtions aren't returning the same thing. The comma separators aren't happening, nor is the 5 decimal places, in the STUFF version.

???? You mean the code I posted doesn't work for you? Works fine for me...

SELECT Amount

INTO #MyHead

FROM (

SELECT 1.1 AS Amount UNION ALL

SELECT 12.12 AS Amount UNION ALL

SELECT 123.123 AS Amount UNION ALL

SELECT 1234.1234 AS Amount UNION ALL

SELECT 12345.12345 AS Amount UNION ALL

SELECT 123456.12345 AS Amount UNION ALL

SELECT 1234567.12345 AS Amount UNION ALL

SELECT 12345678.12345 AS Amount UNION ALL

SELECT 123456789.12345 AS Amount)d

SELECT '$'+LTRIM(REPLACE(STUFF(STUFF(RIGHT(SPACE(15)+CAST(Amount AS VARCHAR(16)),16),8,0,','),5,0,','),' ,',' '))

FROM #MyHead

DROP TABLE #MyHead

(9 row(s) affected)

------------------

$1.10000

$12.12000

$123.12300

$1,234.12340

$12,345.12345

$123,456.12345

$1,234,567.12345

$12,345,678.12345

$123,456,789.12345

(9 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Jeff Moden (4/14/2008)
Matt Miller (4/14/2008)
Jeff Moden (4/14/2008)
I wonder which would work more quickly? I think the CLR might win this one because it uses implicit functions...
As of right now - "straight T-SQL" wins by a nose, but the two funtions aren't returning the same thing. The comma separators aren't happening, nor is the 5 decimal places, in the STUFF version.
???? You mean the code I posted doesn't work for you? Works fine for me...
SELECT Amount
INTO #MyHead
FROM (
SELECT 1.1 AS Amount UNION ALL
SELECT 12.12 AS Amount UNION ALL
SELECT 123.123 AS Amount UNION ALL
SELECT 1234.1234 AS Amount UNION ALL
SELECT 12345.12345 AS Amount UNION ALL
SELECT 123456.12345 AS Amount UNION ALL
SELECT 1234567.12345 AS Amount UNION ALL
SELECT 12345678.12345 AS Amount UNION ALL
SELECT 123456789.12345 AS Amount)d
SELECT '$'+LTRIM(REPLACE(STUFF(STUFF(RIGHT(SPACE(15)+CAST(Amount AS VARCHAR(16)),16),8,0,','),5,0,','),' ,',' '))
FROM #MyHead
DROP TABLE #MyHead
(9 row(s) affected)
------------------
$1.10000
$12.12000
$123.12300
$1,234.12340
$12,345.12345
$123,456.12345
$1,234,567.12345
$12,345,678.12345
$123,456,789.12345
(9 row(s) affected)

It's my port to the test data that isn't working. I'm a tad bit tired today, so my parsing functions aren't firing on all cylinders:)....I had to take it apart and stare at it for a little while.

the statistics (on 10M rows):

--CLR

SQL Server Execution Times:

CPU time = 29609 ms, elapsed time = 30962 ms.

--T-SQL

SQL Server Execution Times:

CPU time = 26375 ms, elapsed time = 26830 ms.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

I knew I was tired....forgot to flag the CLR function as deterministic....that shaves 3 secs or so off of the execution time.

--CLR without the Deterministic flag

SQL Server Execution Times:

CPU time = 29609 ms, elapsed time = 30962 ms.

--CLR with the Deterministic flag

SQL Server Execution Times:

CPU time = 26854 ms, elapsed time = 27420 ms.

--T-SQL

SQL Server Execution Times:

CPU time = 26375 ms, elapsed time = 26830 ms.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?