Something interesting about patindex

Question

Something interesting about patindex

Alan Burstein

SSC Guru

Points: 61141
More actions
October 17, 2017 at 12:59 pm

#340888

This query:
SELECT [charindex] = charindex('',''), [patindex] = patindex('','');
Returns:
charindex patindex 0 1
Can anyone explain this? I would expect both expressions to return 0. I can't find anything in BOL or elsewhere that clears this up.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."
-- Itzik Ben-Gan 2001

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Alan.B - Tuesday, October 17, 2017 12:59 PM
This query:
SELECT [charindex] = charindex('',''), [patindex] = patindex('','');
Returns:
charindex patindex 0 1
Can anyone explain this? I would expect both expressions to return 0. I can't find anything in BOL or elsewhere that clears this up.

PATINDEX finds it, CHARINDEX finds it at position 0.
Easy 😉

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Did you find a real-world problem with this functionality? Or were you experimenting?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLPirate SSCrazy Points: 2880 More actions · Answer 3

Alan.B - Tuesday, October 17, 2017 12:59 PM
Can anyone explain this? I would expect both expressions to return 0. I can't find anything in BOL or elsewhere that clears this up.

charindex here is like a philosophical debate... if you look for nothing inside of nothing and found... nothing... did you find something? SQL says, "no", and returns a 0 because of it.

patindex, on the other hand... at what point does my nothing match nothing? SQL says, "at the beginning", and returns 1... because starting position is 1-based not 0-based.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Luis Cazares - Tuesday, October 17, 2017 1:50 PM
Did you find a real-world problem with this functionality? Or were you experimenting?

Thanks for the reply Luis. Curiosity. It started as a work related problem - I replaced some charindex logic with patindex logic. Occasionally we'd get some instances of charindex('',''). After switching to patindex I got some 1's where I was expecting 0's. It was frustrating but, once I figured that patidex('','')=1 the problem was easy to fix.

Chris & SQL Pirate - Thanks to you both - I get it now. My real question (looking back I worded my question poorly) was, why does charindex('','') not equal patindex('','')?
Part of the my confusion was - if patindex('','')=1 then why does charindex('','') not equal 1? Nonetheless, you guys were both very helpful.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001