PATINDEX

here's my msgID value:  AAAA061206003028314^74842238^QQQQ

the format of the msgID string is this:
  AAAA - endpoint
  061206 - DDMMYY
  003028314^74842238^QQQQ - ordernumber

i'm using this to strip the UNKNOWN number of zeros from the front of the ordernumber:  
(i want this:  003028314^74842238^QQQQ  to be this:  3028314^74842238^QQQQ )

LEFT(SUBSTRING(msgID,1,10)+SUBSTRING(msgID,11+PATINDEX('%[1-9]%',SUBSTRING(msgID,11,LEN(msgID)-10))-1,LEN(msgID) - 11),
LEN(SUBSTRING(msgID,1,10)+SUBSTRING(msgID,11+PATINDEX('%[1-9]%',SUBSTRING(msgID,11,LEN(msgID)-10))-1,LEN(msgID) - 11)) -2)

stupid oversight of mine, i am sure, but why am i getting this:  
AAAA0612063028314^74842238^QQ
instead of this:
AAAA0612063028314^74842238^QQQQ


LVL 18
dbaSQLAsked:
Who is Participating?
 
LowfatspreadCommented:
substring(msgid,1,10)+
convert(varchar(10),convert(int,substring(msgid,11, charindex('^',msgid,11) - 11)))
+substring(msgid,charindex('^',msgid,11),len(msgid)- charindex('^',msgid,11) + 1)
0
 
LowfatspreadCommented:
because of the -2?

wouldn't this be "better"

convert(varchar(10),convert(int,substring(msgid,11, charindex('^',msgid,11) - 11)))
+substring(msgid,charindex('^',msgid,11),len(msgid)- charindex('^',msgid,11) + 1)
0
 
dbaSQLAuthor Commented:
but lowfat, this is my value:  AAAA061206003028314^74842238^QQQQ
your suggestion gives me this:  3028314^74842238^QQQQ      
when i need this:   AAAA0612063028314^74842238^QQQQ

in this particular example, i only need to pull those two 00's after AAAA061206 but before 3028314^74842238^QQQQ
see, there's an unknown number of zeros here.  2, 3, 4...it varies
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dbaSQLAuthor Commented:
perfect.  (and way less typing than mine)
but lowfat, i still don't see why mine wasn't cutting it.  do you?
0
 
Scott PletcherSenior DBACommented:
Or:

LEFT(msgid, 10) + SUBSTRING(msgid, 10 + PATINDEX('%[^0]%', SUBSTRING(msgid, 11, 50)), 50)
0
 
Scott PletcherSenior DBACommented:
Actually, upon reflection, I think I most prefer this:

STUFF(msgid, 11, PATINDEX('%[^0]%', SUBSTRING(msgid, 11, 50)) - 1, '')
0
 
dbaSQLAuthor Commented:
why do you prefetr that, scott?  i'm using this in a reconciliation procedure -- performance and runtime is of the essence.  why is one better than the other?
0
 
Scott PletcherSenior DBACommented:
I think it will perform better.  It's also (much) shorter, and thus easier to interpret and change, IMO.
0
 
LowfatspreadCommented:
a thought occurs what do yo expect to happen if the first number is actually zero...?
0
 
dbaSQLAuthor Commented:

  003028314^74842238^QQQQ - ordernumber

Lowfat, if you mean the first number in the ordernumber is actually a zero, then I just think we're kinda outta luck on that one, for sure.  I actually made the very same statement to the parties that be, but I was dismissed.  There is no guarantee that a legitimate ordernumber won't begin with a zero -- but, at this point, I simply haven't accounted for that.  I think, if that actually does occur, we may be invalidating the ordernumber by stripping the legitimate zeros.  As far as my reconciliation goes, it shouldn't hinder me, as I am running this on both sides of the recon -- so, the string still matches.  But, for obvious reasons, we don't want to invalidate the ordernumbers --- so, I believe I just need to emphasize this just a bit more to a few people, and see what's what.

Is that what you meant?

and scott, it actually does perform better -- runtime, i mean.  a teensy bit faster.  why is that?
0
 
Scott PletcherSenior DBACommented:
Lots fewer functions and no string concatenation; SQL is slow at string concat.
0
 
dbaSQLAuthor Commented:
yes, definitely a bit slower w/the concatenation.  ok, thank you scott
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.