price formatting

[andreas2410]

ok, i have a column of data type money and the data that is found in there is entered like this

1.1490
1.2340
1.0590
0.5990
0.5380

what i want to do is have it formatted as such
1149
1234
1059
0599
0538

ive tried using cast, convert, from money to varchar and simplying multiplying the field, replace and trunc but its not happening !!! any code help would be great!

thanks
andreas

[jai80]

hi andreas,

Try this :

declare @a varchar(10)
declare @b varchar(10)
set @a= convert(varchar(10),1.1490)
set @b=Replace(@a,'.','')
select left(@b,4)
 

Let me know if this has solved your problem. Gud Luck!

Cheers,
jai

ok, i have a column of data type money and the data that is found in there is entered like this

1.1490
1.2340
1.0590
0.5990
0.5380

what i want to do is have it formatted as such
1149
1234
1059
0599
0538

ive tried using cast, convert, from money to varchar and simplying multiplying the field, replace and trunc but its not happening !!! any code help would be great!

thanks
andreas

[almaz]

Recommended pattern is that any data formatting should be performed on the client side.

[almaz]

If you still want to format the data on server, use the following script:

declare @t table (price money)
insert @t values (1.1490)
insert @t values (1.2340)
insert @t values (1.0590)
insert @t values (0.5990)
insert @t values (0.5380)
 
select formatted_price = isnull(replicate('0', 4-len(price)), '') + price
from 
    (select price = convert(varchar(10), convert(int,price*1000)) 
    from @t) source

[andreas2410]

all of the code works perfectly fine, the thing is thought, that in dbo.vwCurrentFuelPrice there has a column CurrentPrice that i want to format in the way i described above. i want to be able to get it every time without having to manually enter the prices 1.1490 etc. thats how it gets put in if you get my drift.. thanks!

[andreas2410]

all of the code works perfectly fine, the thing is thought, that in dbo.vwCurrentFuelPrice there has a column CurrentPrice that i want to format in the way i described above. i want to be able to get it every time without having to manually enter the prices 1.1490 etc. thats how it gets put in if you get my drift.. thanks!

heres the offending code

declare @a varchar(10)
declare @b varchar(10)

SELECT DateEntered, dbo.Site.SiteCode AS SiteCode, ('0' + DepartmentID) AS FuelCode, convert(varchar(10),CurrentPrice)
FROM dbo.vwCurrentFuelPrice, dbo.Site
WHERE dbo.vwCurrentFuelPrice.SiteID = dbo.Site.SiteID

set @a = convert(varchar(10),dbo.vwCurrentFuelPrice.Current Price)
set @b =Replace(@a,'.','')
select left(@b,4)

that should help explain a bit about what im trying to achieve

[iburyak]

Try this:

[PHP]
select right('0000' + convert(varchar(10), convert(int, CurrentPrice * 1000)),4) from dbo.vwCurrentFuelPrice[/PHP]

[andreas2410]

legend! thanks heaps buddy

=)