how to remove leading 1 and zeros

Question

Post reply

how to remove leading 1 and zeros

dastagiri16

Hall of Fame

Points: 3544
More actions
August 27, 2016 at 2:13 pm

#316675

i am getting numbers like as below,
1000000017777
1000000017342
how can i remove leading 1 and zeros (10000000) and get the result like 17777

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Abdallah.mehdoini SSC Veteran Points: 221 More actions · Answer 1

declare @a bigint

set @a='1000000017777'

select replace(cast(@a as varchar(50)),0,'')

Abdallah.mehdoini SSC Veteran Points: 221 More actions · Answer 2

declare @a bigint

set @a='100000000000017777'

select replace(cast(@a as varchar(50)),'0000000000001','')

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Assuming you know there is only one non-zero leading digit, doesn't matter how many zeroes there are

DECLARE @Number BIGINT;

SET @Number = 1000000017777;

SELECT CAST(RIGHT(@Number, LEN(@Number) - 1) AS INT);

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ben Teraberry SSChampion Points: 11845 More actions · Answer 4

Or if the issue is that some system's incremental numbers started at 1 trillion plus 1, you could just subtract 1 trillion from the BIGINT form of your number 😀

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Or if your number is actually character data. 😀

declare @number varchar(20) = '1000000017777'

select @number as before

select @number = RIGHT(@NUMBER,len(@number) - PATINDEX('%0[^0]%',@number))

select @number as After

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

If it's a bigint or similar, then a simple math operation will work.

DECLARE @Table TABLE( Somenumber bigint);

INSERT INTO @Table

SELECT 1000000017777 UNION ALL

SELECT 1000000017342;

SELECT *, Somenumber % 1000000000000

FROM @Table;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sergiy SSC Guru Points: 110208 More actions · Answer 7

dastagiri16 (8/27/2016)
i am getting numbers like as below,
1000000017777
1000000017342
how can i remove leading 1 and zeros (10000000) and get the result like 17777

Just do what's requested:

remove leading 1

STUFF('1000000017777', 1,1,'')

and zeros

CONVERT(int, STUFF('1000000017777', 1,1,''))

_____________
Code for TallyGenerator