Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 convert float to char

Author  Topic 

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-05-23 : 06:01:50
hi, how can I convert a float to a char so I can do:

'£' + convert(char,amount) AS [money]

When I do the above the number is like 1.07848e+006
rather than 1078480


thans for any help.
jamie

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-23 : 06:04:53
[code]Select '£' + convert(varchar(10),convert(numeric(12,2),amount)) AS [money][/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-05-23 : 06:10:10
thats great thanks !
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 06:21:06
[code]declare @f float

set @f = 1078480

select '£' + convert(char,@f) AS jamie,
'£' + convert(varchar(10), convert(numeric(12, 2), @f)) AS harsh,
'£' + LTRIM(STR(@f, 20, 2)) AS PesoNoDecimals,
'£' + LTRIM(STR(@f, 20, 0)) AS PesoWithDecimals[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-05-23 : 08:14:31
peso, thats cool aswell.
thank you.

ps, is it possible to format numbers with commas in SQL ?

eg, 2400 = 2,400

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 08:19:34
Yes, have a look at CONVERT function in Books Online.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-06-03 : 07:23:34
hi, I cannot find how to do this ? can anyone help ? eg, convert 2400 to 2,400

Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-03 : 07:26:33
Really?

select convert(varchar(10), cast(2400 as money), 1)


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 07:31:52
quote:
Originally posted by jamie

hi, I cannot find how to do this ? can anyone help ? eg, convert 2400 to 2,400




What you're trying to do here are formatting of data. Its always better to do this type of formatting at your front end application.

and if you want to do it in sql do like this

SELECT CONVERT(varchar(10),yourmoneyvalue,1)
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-06-03 : 07:45:11
ah, I need to convert it to money !! thank you thank you
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-06-03 : 07:58:04
This added decmial places to the end, eg, 2,400.00. how can I just show 2,400 ?
Is this possible with the money datatype ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 08:00:36
quote:
Originally posted by jamie

This added decmial places to the end, eg, 2,400.00. how can I just show 2,400 ?
Is this possible with the money datatype ?


use LEFT() function
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 08:02:38
You want to go from "1078480" to "£1,078,480" ?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-06-03 : 08:04:26
that is correct peso
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 08:18:56
And there is no other way to accomplish this in your report engine or front-end application?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 08:21:31
[code]DECLARE @f FLOAT

SET @f = 1078489

SELECT LEFT('£' + CONVERT(VARCHAR(20), CAST(@f AS MONEY), 1), LEN(@f) - 2)[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-06-03 : 08:27:36
hi, I just this second worked it out :

select left(ltrim(convert(char(10),cast('20000'as money),1)),
LEN(ltrim(convert(char(10),cast('20000'as money),1)))-3)

a pain though !
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 08:51:52
Yes, it seems simpler to use than the suggestion made 06/03/2008 : 08:21:31


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-06-04 : 04:51:48
hi peso, thank you for your suggestion. I didn't see that before I worked it out, yours is more readable !

one last question on formatting numbers / money. how can I format 5300000 as 5.3
?

thank you for all your help
Jamie
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-04 : 05:51:45
Divide by 100,000 and round to 1 decimal?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-06-04 : 06:01:29
ok, cheers, I'll try that.
Go to Top of Page
    Next Page

- Advertisement -