Aw Hex

[trenttc]

Anyone know the syntax for hex lowecase a-z in patindex? This isn't it:
SELECT @patindex = PATINDEX('%'+'[\x61-\x7A]'+'%',@word);

 

[ESquared]

Um, I thought I had the answer, but it works in one situation and not the other:

PRINT PATINDEX('%[a-z]%', 'Aa')
PRINT PATINDEX('%[a-z]%' COLLATE SQL_Latin1_General_CP1_CS_AS, 'Aa' COLLATE SQL_Latin1_General_CP1_CS_AS)
PRINT PATINDEX('%[A-Z]%' COLLATE SQL_Latin1_General_CP1_CS_AS, 'aA' COLLATE SQL_Latin1_General_CP1_CS_AS)

I was going to say, use the appropriate case-sensitive collation for the database you are in. Find the collation for the column you'll be comparing and switch the CI to CS.

Now I don't know what's going on as the 3rd print statement above is giving the wrong answer.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[gmmastros]

Maybe a binary collation would work out a little better. I don't know why the case sensitive collation is not producing the right results.

PRINT PATINDEX('%[a-z]%', 'Aa')
PRINT PATINDEX('%[a-z]%' COLLATE Latin1_General_BIN, 'Aa' COLLATE Latin1_General_BIN)
PRINT PATINDEX('%[A-Z]%' COLLATE Latin1_General_BIN, 'aA' COLLATE Latin1_General_BIN)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[trenttc]

A patindex statement can search for a range [a-z],[0-9], etc. I was wondering if it's possible to for a patindex statement to search for a hex range (without collate, etc)?

 

[SQLDenis]

one more

PRINT PATINDEX('%[a-z]%', 'Aa')
PRINT PATINDEX('%[a-z]%' COLLATE Latin1_General_CS_AS_KS_WS, 'Aa' COLLATE Latin1_General_CS_AS_KS_WS)
PRINT PATINDEX('%[A-Z]%' COLLATE Latin1_General_CS_AS_KS_WS, 'aA' COLLATE Latin1_General_CS_AS_KS_WS)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

There's no difference between a character range and a hex range. Put the hex range in as '%[' + Char(x) + '-' + Char + ']%' if you need to. It works. You can match carriage returns, Ascii 0 characters, whatever. And using a binary collation probably is the best way to go as George suggested. Just note that you have to handle the characters ] and - specially. For ] double them up ]] and for - I think it has to be the first character but I am not sure, experimentation is in order.

I don't know the collation that SQLDenis suggested. How do I find these collations and what they do, Denis?

I'd also love to hear an explanation for why my offering works for lower case letters but not upper case letters.