check value in table upper case or lower

hi

i want to select * from table1 where name =petter?

now if there is many type of petter in table linke PETTER ,Petter Andpetter which record will come in display?

if i want all this three (PETTER,Petter,petter) will come in display which command is for this ?

regard

It depends on the column collation(default the same as database) setting, which you can check usingsyscolumns. If you want to ignore case, you can change all data into UPPER (or LOWER) case:

DECLARE @.n1 varchar(20)
SELECT @.n1='petter'
SELECT * FROM testCol where UPPER(name)=UPPER(@.n1)

|||

Or you can specify the collation used in the SELECT command:

DECLARE @.n1 varchar(20)
SELECT @.n1='petter'
SELECT * FROM testCol wherename=@.n1
collate SQL_Latin1_General_CP1_CI_AS

You can get descriptions of SQL collations using this statement:

SELECT * FROM ::fn_helpcollations()

|||

that was a good solution

but what it does

collate SQL_Latin1_General_CP1_CI_AS

any ideas

|||

This is used to specify collation used in the SELECT command. I choose SQL_Latin1_General_CP1_CI_AS because this collation is case insensitive (notice the CI). For more information, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5ell.asp