hi,
i have a sql table field price and datatype is
decimal 13(20,6).
when i insert values to this field, values are being
inserted correctly. i.e. 13.45 inserted as 13.45 and
145.653 inserted as 145.653 only.
But while fetching only the values are coming as 13.450000,
145.653000, because the datatype is decimal 13(20,6) with
6 decimals. but i want 13.45, 145.653 as in the table.
How suppress the unwanted zeroes at the end of those
numbers.
any help.
thanks,
hari.
see following example:
drop table test
create table test(c1 decimal (15,5))
insert into test values (3.567000)
insert into test values (232233.567000)
insert into test values (3.567)
query:
select c1,reverse(substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) - 1)
)) 'no_zeros'
from test
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||hi thanks,
but it is not working for whole number like ex:11120
it is giving it as 11120. (with point at the end)
how to do that.
thanks,
hari.
>--Original Message--
>see following example:
>drop table test
>create table test(c1 decimal (15,5))
>insert into test values (3.567000)
>insert into test values (232233.567000)
>insert into test values (3.567)
>query:
>select c1,reverse(substring(reverse(cast(c1 as varchar
(25))) ,
>patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
>len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as
>varchar(25)))) - 1)
>)) 'no_zeros'
>from test
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||try:
select c1, reverse(
case when substring(substring(reverse(cast(c1 as varchar(25)))
,
patindex('%[^0]%', reverse(cast(c1 as varchar(25))))
,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1)) ,1,1) = '.'
then
substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) + 1 ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 2))
else
substring(reverse(cast(c1 as varchar(25))) ,
patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) ,
len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1))
end)
'no_zeros'
from test
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"hari" <anonymous@.discussions.microsoft.com> wrote in message
news:207601c4a145$b5262d90$a601280a@.phx.gbl...[vbcol=seagreen]
> hi thanks,
> but it is not working for whole number like ex:11120
> it is giving it as 11120. (with point at the end)
> how to do that.
> thanks,
> hari.
> (25))) ,
> reverse(cast(c1 as
|||See if this works:
select
replace(rtrim(replace(
replace(rtrim(replace(
c1,'0',' ')),' ','0')
,'.',' ')),' ','.')
from test
Steve Kass
Drew University
hari wrote:
[vbcol=seagreen]
>hi thanks,
> but it is not working for whole number like ex:11120
> it is giving it as 11120. (with point at the end)
> how to do that.
>thanks,
>hari.
>
>(25))) ,
>
>reverse(cast(c1 as
>
>
Showing posts with label zeroes. Show all posts
Showing posts with label zeroes. Show all posts
Monday, March 12, 2012
how to suppress zeroes after decimal and whole no. should remain same
hi ,
but it is not working for whole number like ex:11120
it is fetching it as 11120. (with point at the end)
how to do that.
thanks,
hari.
>--Original Message--
>see following example:
>drop table test
>create table test(c1 decimal (15,5))
>insert into test values (3.567000)
>insert into test values (232233.567000)
>insert into test values (3.567)
>query:
>select c1,reverse(substring(reverse(cast(c1 as varchar
(25))) ,
>patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
>len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as
>varchar(25)))) - 1)
>)) 'no_zeros'
>from test
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>hi,
> i have a sql table field price and datatype is
>decimal 13(20,6).
> when i insert values to this field, values are being
>inserted correctly. i.e. 13.45 inserted as 13.45 and
>145.653 inserted as 145.653 only.
>But while fetching only the values are coming as
>13.450000,
>145.653000, because the datatype is decimal 13(20,6)
>with
>6 decimals. but i want 13.45, 145.653 as in the table.
> How to suppress the unwanted zeroes at the end of those
>numbers.
>any help.
thanks,
hari.
>.
>
check my other reply.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||You can try the following expression:
LEFT( c, LEN( c ) - PATINDEX( '%[1-9]%', REVERSE( c ) ) + 1 )
: where c is your column
Anith
|||thanks Steve,
it's working fine for removing zeroes and formating it
with commas. i changed it with combining ur previous and
current answers.
rgds,
hari.
>--Original Message--
>Here is another option:
>select
> parsename(c1,2)
>+ coalesce('.'+reverse(0+nullif(reverse(parsename
(c1,1)),0)),'')
>from test
>SK
>hari wrote:
>.
>
but it is not working for whole number like ex:11120
it is fetching it as 11120. (with point at the end)
how to do that.
thanks,
hari.
>--Original Message--
>see following example:
>drop table test
>create table test(c1 decimal (15,5))
>insert into test values (3.567000)
>insert into test values (232233.567000)
>insert into test values (3.567)
>query:
>select c1,reverse(substring(reverse(cast(c1 as varchar
(25))) ,
>patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
>len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as
>varchar(25)))) - 1)
>)) 'no_zeros'
>from test
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>hi,
> i have a sql table field price and datatype is
>decimal 13(20,6).
> when i insert values to this field, values are being
>inserted correctly. i.e. 13.45 inserted as 13.45 and
>145.653 inserted as 145.653 only.
>But while fetching only the values are coming as
>13.450000,
>145.653000, because the datatype is decimal 13(20,6)
>with
>6 decimals. but i want 13.45, 145.653 as in the table.
> How to suppress the unwanted zeroes at the end of those
>numbers.
>any help.
thanks,
hari.
>.
>
check my other reply.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||You can try the following expression:
LEFT( c, LEN( c ) - PATINDEX( '%[1-9]%', REVERSE( c ) ) + 1 )
: where c is your column
Anith
|||thanks Steve,
it's working fine for removing zeroes and formating it
with commas. i changed it with combining ur previous and
current answers.
rgds,
hari.
>--Original Message--
>Here is another option:
>select
> parsename(c1,2)
>+ coalesce('.'+reverse(0+nullif(reverse(parsename
(c1,1)),0)),'')
>from test
>SK
>hari wrote:
>.
>
Subscribe to:
Posts (Atom)