PATINDEX bug?

Question

PATINDEX bug?

kung lao

SSC Veteran

Points: 296
More actions
July 24, 2007 at 9:44 am

#95378

This is really wierd and I hope someone can shed some light -
Why does doing this pattern search hit on the lower case 'a'?
SELECT PATINDEX ('%[¾-À]%', 'abcdefghijk')
Same this as doing:
declare @Pattern varchar(250)
set @Pattern = '%['+char(190)+'-'+char(192)+']%'
SELECT PATINDEX (@Pattern, 'abcdefghijk')
Now if I look for these characters individually, it does not find anything..Is this an MS bug?
Any help is appreaciated, thanks..

Viewing 15 posts - 1 through 15 (of 15 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

This may actually an easy one. You are not looking for two characters. You are looking for a range of characters. I'll bet if you check, the lower case a falls between the 3/4 and A with the tilde.

-SQLBill

kung lao SSC Veteran Points: 296 More actions · Answer 2

Actually no, the ascii char code for lower case "a" is 97. The range I was looking in was from 190 - 192.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Here is my guess, you have a case insensitive collation, therefore 'a' = 'A'. I ran you select on my system and it returns a 0, and I use a case sensitive collation.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Possibly also Accent Insensitive on your collation.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Case insensitive collation, ran it on one of our servers with a case insensitve collation and it hit on the 'a'.

kung lao SSC Veteran Points: 296 More actions · Answer 6

After pulling some hairs I found that char(192)À is actually made up of char(97) a and char(145) ‘ so when I do a "like" search it hits on a, because technically it's made up of that character. Computers..eh'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I don't think that has anything to do with it. What is the collation of your server? I think you will find it is set to a case insensitive collation.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

Nope... not a PATINDEX bug... and you don't need to mess with the server default settings (case sensitive servers are a huge pain!).

This will do it for you...

SELECT PATINDEX ('%[¾-À]%', 'bcdeafg' COLLATE Latin1_General_BIN )

The "Latin1_General_BIN" collation requires a match at the BINary level...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Jeff, I wasn't suggesting that server collation be changed, just that it be checked. We have several PeopleSoft servers and they are set to Latin1_General_BIN, so when I started looking at our Data Warehouse, I choose Later_General_BIN2 for the collation there. Plus, although our other systems (SIS) are case insensitive, all the data is mixed case. We went the case sensitive.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 10

Shoot, Lynn... I knew that... you knew that... I just wanted to make sure Kung Lao knew that. Whether anyone suggests changing the server collation or not, I always post that you don't have to to solve this simple problem because some people just don't know about the COLLATE clause and end up going off the deep end at the server level.

Kung Lao... are you all set, now?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Jeff, Just making sure, some could take it that I was. I didn't test throwing in the collate in the select on the case insensitive server to see if that would help, but I could tomorrow if asked.

l8r Jeff!

kung lao SSC Veteran Points: 296 More actions · Answer 12

kung lao

SSC Veteran

Points: 296

July 25, 2007 at 3:22 pm

#722429

Jeff that does it, you tha man - thanks!

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 13

Thanks for the feedback, Kung Lao.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kung lao SSC Veteran Points: 296 More actions · Answer 14

kung lao

SSC Veteran

Points: 296

July 30, 2007 at 6:57 am

#723282

Jeff, you're right on. Thanks!