Remove Zero - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Remove Zero

Author

Topic

samir.first
Starting Member

34 Posts

Posted - 2014-08-20 : 07:57:48

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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-20 : 08:43:18

[code]
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)
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-08-20 : 09:07:29

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

samir.first
Starting Member

34 Posts

Posted - 2014-08-22 : 16:15:00

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-23 : 08:26:30

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-23 : 11:08:26

First question is "Why do you care about trailing zeros?".

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

samir.first
Starting Member

34 Posts

Posted - 2014-08-24 : 01:33:31

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-24 : 04:16:48

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources