Case Sensitivity in SQL 1

[EricDraven]

Is there anyway in SQL of searching a field without case sensitivity? For example:

Select * from MyTable
where Surname = "MacDougal"

would return exactly the same results as

Select * from MyTable
where Surname = "Macdougal"

and all other capitalisation variations of the name. I arent worried about spelling, just the case of the surname.

Many thanks in advance


When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[jimbopalmer]

Select * from MyTable
where upper(Surname) = 'MACDOUGAL'

or

Select * from MyTable
where upper(Surname) = upper('Macdougal')

I tried to remain child-like, all I acheived was childish.

 

[EricDraven]

Many thanks indeed, that seems to do exactly what it says on the tin!


When your feeling down and your resistance is low, light another cigarette and let yourself go

 

[JarlH]

You can also use a case insensitive collation when you are doing the comparison. Like:

Select * from MyTable
where Surname = 'MacDougal' collate some_collation;

The collation names are vendor specific. For example MS SQL Server has a collation called ENGLISH_CI_AI (?) you can use. Mimer SQL's collation ENGLISH_1 is also case insensitive.

Using a collation that is both case and accent insensitive will in most cases give a more accurate result than the UPPER solution.

For example, if you have Muller and Müller an accent insensitive collation will treat them as equal when doing the comparison.

/Jarl