patindex not returning expected value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

patindex not returning expected value

Author

Topic

lholden
Starting Member

5 Posts

Posted - 2006-08-02 : 05:02:02

can anyone please explain to me why the following query does not return the value 1?

select patindex('%[^a-zA-Z0-9&@_.-]%', 'ºerif@hotmail.com')

I can clearly see that the first character is not in the specified list. The ascii value of the character is 186, yet SQL thinks that is is between both a-z AND A-Z

I have tried this both 2000 & 2005 with the same result (0)

Any help would be much appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-02 : 05:06:27

Did you try this ?

select patindex('%[^a-zA-Z0-9&@_.-]%', char(186) + 'erif@hotmail.com')

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 05:06:43

I get 1 here, so must be the collation you are using which is treating ASCII 186 as equivalent to one of the characters in your RegEx pattern.

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-02 : 05:09:45

or specify the string as unicode

select patindex('%[^a-zA-Z0-9&@_.-]%', N'ºerif@hotmail.com')

It gives you the result that you want but I can't explain why is this so.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-02 : 05:11:45

quote:
Originally posted by Kristen

I get 1 here, so must be the collation you are using which is treating ASCII 186 as equivalent to one of the characters in your RegEx pattern.

Kristen

I am using Chinese_PRC_BIN and i got a 0 with the original query.

lholden
Starting Member

5 Posts

Posted - 2006-08-02 : 06:43:23

thanks for the suggestions:
I've tried char(186) and specifying that it is unicode but still get 0.

Kristen, can I ask what collation you are using?
I'm on Latin1_General_CI_AS.
I've also tried it on a server that is SQL_Latin1_General_CP1_CI_AS with the same results.

I also don't really want to specify anything that would be specific to this example i.e.

select patindex('%[º^a-zA-Z0-9&@_.-]%', 'ºerif@hotmail.com')

as it needs to be a generic thing for checking that an email address contains valid characters.

lholden
Starting Member

5 Posts

Posted - 2006-08-02 : 06:51:44

aha, sorry guys, tried this on SQL_Latin1:
select patindex('%[^a-zA-Z0-9&@_.-]%', N'ºerif@hotmail.com')
and it returned 0

just tried:
select patindex('%[^a-zA-Z0-9&@_.-]%', 'ºerif@hotmail.com')
and it returned 1

must be a unicode char that falls between a and z.

question now is how do i ensure that it rejects regardless of the collation?
I suppose i could pull all of the data into a temp table and specify the collation of the email column to be SQL_Latin1...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-02 : 07:20:25

You can also do the collation trick on the fly.

Without this trick, I also get 0 as response, but with this

select patindex('%[^a-zA-Z0-9&@_.-]%' COLLATE SQL_Latin1_General_Cp1_CI_AS, 'ºerif@hotmail.com' COLLATE SQL_Latin1_General_Cp1_CI_AS)

I get 1 as response from PATINDEX.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources