Convert SQLS-float to formatet String

Question

Convert SQLS-float to formatet String

info 58414

Hall of Fame

Points: 3386
More actions
February 5, 2013 at 1:29 pm

#268128

Hello,
I want to convert float values in Char-format (5.2)
3 digits before / 2 digits after. Without dot or comma and leading zero-values:
0.12 = 00012
2.3 = 00230
12.3 = 01230
123.4 = 12340
2 = 00200
NULL = 00000
and on so ....
Thank you
reagards
Nicole :w00t:

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

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

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 1

Well this seems like a really silly thing to do, so I want to ask, why?

But I guess if you really need to, this will work (note it will not work if your column has any numbers with 4 or more digits to the left of the decimal):

select right('00' + replace(convert(varchar(6), convert(decimal(5,2), isnull(mynum, 0))), '.', ''), 5)

from mytable

info 58414 Hall of Fame Points: 3386 More actions · Answer 2

you ask me why ?

I have to Export this Values from SQLS to ADABAS C.

🙂

thank you !!

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 3

Well I won't be of any help to you on the export since I've never dealt with ADABAS, but hopefully that little piece of code helps you.

ScottPletcher SSC Guru Points: 101147 More actions · Answer 4

CAST(CAST(CAST(ISNULL(char_value, 0) AS decimal(5, 2)) * 100 AS int) AS varchar(5))

SELECT

CAST(CAST(CAST(ISNULL(char_value, 0) AS decimal(5, 2)) * 100 AS int) AS varchar(5))

FROM (

SELECT '0.12' AS char_value -- = 00012

UNION ALL

SELECT '2.3' -- = 00230

UNION ALL

SELECT '12.3' -- = 01230

UNION ALL

SELECT '123.4' -- = 12340

UNION ALL

SELECT '2' -- = 00200

UNION ALL

SELECT NULL --= 00000

) AS test_values

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".