Export SQL unique format

Question

Post reply

Export SQL unique format

mcieslinski

Old Hand

Points: 374
More actions
May 2, 2008 at 1:37 pm

#189848

I have a problem with file export. I need to create a process to create and extract file and the format is new to me. For the currency fields the end user expects the fields to have an implied decimal point, be zero filled to the field width and have a trailing sign (either a '-' for negative or space for positive) example:
$102.50 would be '00010250 '
-$57.75 would be '00005750-'
Is there a standard format in SQL, either DTS or t-SQL that can accomplish this? I figure I can multiply each number by 100 to get rid of the decimal; write a case statement to get the sign and handle the 0 fill. This seems like a lot of work.
Thanks in advance for any help!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Your way or create a function to do it.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

Don't need a case statement to figure the zero's...

DECLARE @DemoTable TABLE (Amount VARCHAR(15))

INSERT INTO @DemoTable (Amount)

SELECT '$102.50' UNION ALL

SELECT '-$57.75'

SELECT REPLACE(STR(ABS(CAST(Amount AS MONEY)*100.0),8),' ','0')

+ CASE

WHEN CAST(Amount AS MONEY) < 0

THEN '-'

ELSE ' '

END

FROM @DemoTable

This could actually be used in a computed column in a table or you could turn it into a function.

--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)

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

Sorry, my mistake... you don't need a CASE at all...