SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Remove Zero
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

samir.first
Starting Member

Egypt
34 Posts

Posted - 08/20/2014 :  07:57:48  Show Profile  Reply with Quote
How to Remove increase Zero from Number after Digit
125,1250000000 To 125,125
1256000,25630000 To 126000,2563
16800,0002502550000 TO 16800,00250255

gbritton
Flowing Fount of Yak Knowledge

1445 Posts

Posted - 08/20/2014 :  08:43:18  Show Profile  Reply with Quote

declare @number int = 1250000000;
declare @string varchar(10) = @number
declare @firstnonzero int  = patindex('%[1-9]%', reverse(@string))
select @number, @string, @firstnonzero, substring(@string, 1, 1+len(@string) - @firstnonzero)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 08/20/2014 :  09:07:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
One of the methods

declare @t varchar(100)
set @t='16800,0002502550000'
select @t, substring(@t,1,len(@t)-patindex('%[1-9]%',reverse(@t))+1)


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

samir.first
Starting Member

Egypt
34 Posts

Posted - 08/22/2014 :  16:15:00  Show Profile  Reply with Quote
thank you madhivanan
but I need remove Zero in Decimal point only
Example
16000,002500 TO 16000,0025
16000 TO 16000
declare @t varchar(100)
set @t='16800'
select @t, substring(@t,1,len(@t)-patindex('%[1-9]%',reverse(@t))+1)
The result 168 this wrong value
and I need data type Value is decimal Not varchar
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1445 Posts

Posted - 08/23/2014 :  08:26:30  Show Profile  Reply with Quote
Makes no sense. Decimal stores the precision you give it. There are no zeros to remove... Ever!

BTW you never mentioned decimal places before. I feel I'm wasting my time here. OTOH with the solutions you've been given,you should be able to easily do the rest.

Edited by - gbritton on 08/23/2014 08:29:13
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/23/2014 :  11:08:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
First question is "Why do you care about trailing zeros?".



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

samir.first
Starting Member

Egypt
34 Posts

Posted - 08/24/2014 :  01:33:31  Show Profile  Reply with Quote
thank you SwePeso
the answer on your question is
the users are very annoyed from these zero's and can't easily read the number
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/24/2014 :  04:16:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
My second question is "Why do you store the zeros in the first place?".
My guess is that you are using wrong datatype such as varchar to store the data.
Why not use the correct datatype? For example NUMERIC(18, 8)?
Or if the precision does not matter that much, REAL or FLOAT?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000