Is PATINDEX wrong or am I just a gimp?
-
OK, who noticed my deliberate mistake? Here's a useful tip for you. Always make your char and varchar variables big enough to hold the strings you want to put into them. Also, don't go rushing into posting things to forums when the problem is quite clearly down to incompetence
Truly I am the greatest gimp of all.
------------------------------------------------------------------------------------------------------
This is more out of interest than anything else. Could anyone suggest why I get the following behaviour with PATINDEX. Is it because of a global setting I need to enable or does it just have trouble with non-letter characters (numbers and other things like ':' and '[')?
declare @memo varchar (20)
declare @pattern varchar(20)
set @memo = 'Replaces inspection : 51'
--This returns 0
set @pattern = 'inspection :'
select PATINDEX('%' + @pattern + '%',@memo) As One
--This returns a valid index (1 obviously)
set @pattern = 'Replaces inspection :'
select PATINDEX('%' + @pattern + '%',@memo) As Two
--This returns 0
set @pattern = '51'
select PATINDEX('%' + @pattern + '%',@memo) As Three
--This returns a valid index (1 obviously)
set @pattern = 'Replaces inspection : 51'
select PATINDEX('%' + @pattern + '%',@memo) As Four
--This returns 0
select PATINDEX('%Replaces inspection : 51%' ,@memo) As Five
--This returns 0
select PATINDEX('Replaces inspection : 51' ,@memo) As Six
-
Further more to this. I've just found that if I put the non-letter characters in between letter characters it will return an index
declare @b-2 varchar(20)
set @b-2 = 'Replaces : inspection : 51'
select patindex('%:%', @b-2) -- Returns index 10
It's almost like PATINDEX looks for the last letter character and ignores everything after that, including numbers.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply