Convert Decimal(18,6) field to Char(11)

Question

Convert Decimal(18,6) field to Char(11)

clarmatt73

SSCrazy

Points: 2297
More actions
July 21, 2010 at 9:25 am

#387404

Hi All,
Could anyone help me with the code to convert Decimal(18,6) format into char(11) for an export. I need to get figures like 11.240000 to look like 00000001124.
Cheers
Matt

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

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

clarmatt73 SSCrazy Points: 2297 More actions · Answer 1

Sorry forgot to mention, the figures will vary so you may get 100.290000 or 1000.290000 which would need to look like 00000010029 and 00000100029

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT MyPrettyNumber = REPLACE(STR(MyNumber*100,11,0), ' ', '0')

FROM (

SELECT MyNumber = CAST(11.24 AS DECIMAL(18,6))

UNION ALL SELECT 100.290000

UNION ALL SELECT 1000.290000

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

clarmatt73 SSCrazy Points: 2297 More actions · Answer 3

clarmatt73

SSCrazy

Points: 2297

July 21, 2010 at 9:42 am

#1196261

Thanks Chris, that worked a treat.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

You're welcome Matt, thanks for the feedback.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden