[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

a little CASE help, please

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
from database

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?
0
RLLewis
Asked:
RLLewis
1 Solution
 
adwisemanCommented:
select SUBSTRING(case when charindex('_',ordernumber)>1 then left(ordernumber,charindex('_',ordernumber) -1) else ordernumber end
, PATINDEX('%[1-9]%', ordernumber), LEN(ordernumber)) as ordernumber
from (Select '00008642_S' ordernumber) as t

I used a Patindex to search for the first use of numbers [1-9], so everything else at the beginning other than a number 1 - 9 will get stripped off in the substring.
0
 
RLLewisAuthor Commented:
oh fabulous adwise!  that is precisely what i needed.  thank you very much.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now