CODEWITHSUNDEEP
sqlsql-server
Sadat Mainuddin
Sadat Mainuddin

Reputation: 309

Add leading zero and convert as varchar to a float

I have data where I need to add leading zeros to it. But the problem is the data type is float. So whenever I add zeros, it automatically omits them. I have tried to add leading zero to it then try to convert it to varchar(50).

I used following query (thanks to Mr. Damien_The_Unbeliever):

select  '0' + convert (varchar(50), (wallet_sys)) as wallet_sys  from NewSysData1

However the result is showing: 01.72295e+009 instead of showing like this: 01718738312

What should I do?

PS: Some of the sample data are below: 

1718738312, 8733983312, 9383879312

I want these to be: 

01718738312, 08733983312, 09383879312

Upvotes: 0

Views: 4500

Answers (4)

gvee
gvee

Reputation: 17161

From the manual: http://technet.microsoft.com/en-us/library/ms187928.aspx

Syntax for CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

-

float and real Styles

When expression is float or real, style can be one of the values shown in the following table. Other values are processed as 0.

-

Value          | Output
---------------+---------------------
0 (default)    | A maximum of 6 digits. Use in scientific notation, when appropriate.
1              | Always 8 digits. Always use in scientific notation.
2              | Always 16 digits. Always use in scientific notation.
126, 128, 129  | Included for legacy reasons and might be deprecated in a future release.

Note that none of the formatting options allow for any longer values that aren't in scientific notation.

Therefore what we have to do is convert to another type as an interim:

SELECT '0' + Convert(varchar(50), Convert(decimal(38, 10), Convert(float, '1.72295e+009'), 0))

Adjust the number of decimal places by changing the scale of the decimal type

Upvotes: 0

user2065377
user2065377

Reputation: 458

select right(replicate('0',10)+cast(wallet_sys as varchar),11)

Upvotes: 0

crownjewel82
crownjewel82

Reputation: 437

I have done this using the following:

select right('00' + convert(varchar, MONTH(getdate())),2)

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

you can use the str() function and trim

select '0' + ltrim(rtrim(str(wallet_sys))) as wallet_sys from NewSysData1;

SqlFiddle

Upvotes: 2

Related Questions