a little CASE help, please
Posted on 2005-04-28
Hi there. I've got a proc that presently uses this CASE stmt to drop the last two characters of an ordernumber --- _B and/or _S. I need to use very similar logic to do the same thing, but with one additional requirement. this is my stmt:
select case when charindex('_',ordernumber)>1 then left(ordernumber,charindex('_',ordernumber) -1) else ordernumber end as ordernumber
so this: 000008915_B
becomes this: 000008915
very good. but, i need to also drop the zeros that preface the 8915. see, there is an unknown number of zeros prefacing the order number, i need them gone such that this: 000008915_B
becomes this: 8915
sometimes it's one, it's three or four....like i said, it's an 'unknown' number of zeros. i just need to strip the prefacing zeros.
can anybody help me out w/this?