There was a question in the forums where a user asked for a solution to find out the number which is succeeded by the last 0 in that number
For example, in numbers like 10807023 and 1000508, 23 and 8 are the numbers which are succeeded by the last 0 of that number. There can be several solutions and here is the one.
declare @t table(num int) insert into @t select 1000002 union all select 1000032 union all select 400073 union all select 19802 union all select 52 union all select 502065 select num, right(num,case when string_len=0 then total_len else string_len-1 end) as new_num from ( select num, len(num) as total_len, patindex('%[0]%',cast(reverse(num) as varchar(100))) as string_len from @t ) as t
num new_num ----------- ------------ 1000002 2 1000032 32 400073 73 19802 2 52 52 502065 65
Tags:
Is this just an abstract problem or does it come from the real world somewhere? I would be curious to know.
I do not remember the link for this question however the user specified it is a real world problem where they need to make some decisions based on the the last non-zero digits. My guess is that this may be a flag where each non-zero digits indicate some state and they are seperated by zero.
My Answer to this is
SELECT REVERSE(SUBSTRING(CAST(REVERSE(num) as varchar(max)), 1, CHARINDEX('0',REVERSE('0'+ cast(num as varchar(max))))-1)) from @t
I'd use the following:
select ltrim(right(replace(num,'0',space(20)),20)) from @t
It doesnt have to be 20 spaces. At most len(num).
Leonid. Yes, that's a nice one. I don't think anyone will make this any shorter - measured in characters.
Leonid Koyfman, thats very nice solution.
Leonid, I have learned. Thanks a lot. It is one of a good technique
@dishdy, you challenged me. I'm not a big proponent of implicit conversion, but measured in charachters this one is shorter:
select 1*(right(replace(num,'0',space(20)),20))from @t
Leonid, you can get rid of a pair of parenthesis from your new solution as follows:
select 1*right(replace(num,'0',space(20)),20)from @t
I like the way you got rid of that space before 'from'.
@dishdy ... and apostrophes around 0.
select 1*right(replace(num,0,space(20)),20)from @t
I believe that's it.
BTW. I learnt about space before 'from' from Madhivanan.