Solved

PATINDEX

Posted on 2006-06-27
12
610 Views
Last Modified: 2009-07-29
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


0
Comment
Question by:dbaSQL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16995529
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 16995619
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 16995668
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 17

Author Comment

by:dbaSQL
ID: 16995745
perfect.  (and way less typing than mine)
but lowfat, i still don't see why mine wasn't cutting it.  do you?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 16995917
Or:

LEFT(msgid, 10) + SUBSTRING(msgid, 10 + PATINDEX('%[^0]%', SUBSTRING(msgid, 11, 50)), 50)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 16995953
Actually, upon reflection, I think I most prefer this:

STUFF(msgid, 11, PATINDEX('%[^0]%', SUBSTRING(msgid, 11, 50)) - 1, '')
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16996746
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 16996951
I think it will perform better.  It's also (much) shorter, and thus easier to interpret and change, IMO.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16998733
a thought occurs what do yo expect to happen if the first number is actually zero...?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17000055

  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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17001981
Lots fewer functions and no string concatenation; SQL is slow at string concat.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17002038
yes, definitely a bit slower w/the concatenation.  ok, thank you scott
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
connection to SQL 2012 error in windows 10 18 49
T-SQL: Number of Records is Greater Than One 7 51
SQL: Transformation or Pivot 3 36
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question