query

The WHERE Clause: Case Sensitivity and Collations



Sharing buttons:

now earlier when we were talking about

the like operator we talked about the

idea of a collation we talked about case

sensitivity and accent sensitivity and I

want to show you how you can control

that information inside your SQL

statements so to do that we have to

learn a new piece of the puzzle here

there is a term that collate statement

the collate allows you to override at

both the where clause and order by what

the case sensitivity or accent

sensitivity is for that predicate or

that sort

okay so collate allows you to override

column level collation for where and

order by clauses so if you'll remember

we were doing find all was it I think

product names that have a I think that

was our example we did with our

frequently asked question about case

sensitivity and like so the question you

know was is it a capital A or a

lowercase a is it an a with an accent

which a is it and what we said I'll just

write the query we remember this and

what we said is it's not that the like

operator is case and accent agnostic

it's really the column that you're the

expression that you were comparing it

against that defines what the collation

is and so we went to the actual column

and we scripted it out if you remember

doing this we scripted this out and we

saw that the name column was collated

with this particular collation so when

we said that the C I meant case

insensitive and the a s

stood for accent sensitive so an a with

an accent would be read differently from

an a without an accent for example those

would not match if we just said the

letter a write an a with an accent is

not a letter a it's an a with an accent

accent insensitive they would match if

it had a base of a letter a any

variation of the letter a would have

been returned and I told you that I was

going to show you how you can change the

default and that's what we're talking

about now when we do this let's just

order it by name since a will sort first

we're going to get the the two A's here

notice that we get the bike wash the

cable lot these are not capital A's okay

that's because it was C I the collation

of the name column was a shooting let's

see I case-insensitive

therefore we only return we get both

date capitals and the lower cases so

let's talk about how to use the collate

clause so list all collations

on server is the first place you want to

start so there is a function a built-in

function at the end help collation as

you can see it pop up right here it's a

what we call a table valued function it

does require the parentheses like all

functions do chapter 3 the next chapter

is all about using functions and so go

ahead and execute this be aware on

sequel 2005 this probably will not be

instant but this will list you every

collation that you have on the server

and so you have two parts the name in

the description when I went over here

when I went to the table and I asked it

to script the table so I created it

lists the name column here okay so it

says that the name just coincidence the

name column here uses this collation so

what I could do I can copy that come

over here and say where name equal

so if I want to get a description of

what that collation is I can see that it

uses the Latin one general character set

it's case insensitive and accent

sensitive okay there's more to it but

that's the base of what we want okay

so how about this let's now list all

Latin one general case sensitive

collation but how would we do that same

kind of thing right I'm gonna copy that

as my face

we're description like Latin one general

and I'm just copying the format here and

notice what I'm doing I'm not making

another like case sensitive

see case sensitive follows Latin one

general in the description so that's

what I'm returning I could have made two

but this is easiest right I could have

done it like this and description like

case sensitive whichever is clearer to

you you do that one this is a static set

of data that I know matches this

particular pattern here so I'm

comfortable actually hard coding that in

but so there's our statement this is all

of the Latin one general case sensitive

collation

you can see the CS instead of the C I so

now let's go pick one of these let's

just choose this basic one here I will

copy the name and here's how we change

this so I'm going to copy down here that

have a capital A I'm gonna change my

product or my request come back over

here copy the name and here's how you do

your collate sequence

you just simply say kool-aid and you put

that particular collation you want so

now you are telling this predicate that

when it evaluates a row for true or

false evaluate it using this collation

override whatever the collation is of

the name column and make this evaluation

using this case insensitive accent

insensitive and so when we run this now

we only return those rows that have a

capital A that's the idea of using

collisions now you can also do this in

the order by okay so I could come down

here and say I want it collated this way

here and so now it's gonna override

whatever the name columns collation is

and that sorting is going to be done

using this particular collation which

won't affect this for this particular

example but like if I wanted to find

some binary collation maybe that will do

it let's drive Albanian VI n what I'm

trying to do is you notice that all in a

WC sort appropriately I'm trying to flip

them I'll sort it by the binary is what

the bin stands for and sure enough the

capital W in binary comes before the

lowercase L so that two A's are equal

but then it goes to that the top there's

a tie at the first position goes to the

second position and in binary code

capital letters come before lowercase so

therefore it's sorts like this so pretty

cool that you can do that kind of stuff

you know it's not so often that you need

to know but this is one of those areas

where you know with you need to know it

now you do

you