lower case

[drallabs]

What SQL syntax would I use to search for only lower case letters in a given field. This is done in SQL 2005. The field should only have upper case. I need to convert the lower case to upper case.

 

[gmmastros]

Searching for lower case characters is a bit... not fun. It involves collations and such. Perhaps you don't really care to find them. Simply setting the data to UPPER CASE will suffice.

[tt]
Update Table
Set Column = Upper(Column)
[/tt]

If you really do want to search for lower case characters, let me know and I will help you with that.


-George

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

 

[drallabs]

Thanks George I thought it would be easy. If you can post some simple code that would be great but if it will fairly involved thanks.

 

[gmmastros]

There are various collations that can be used. The different collations identify how strings are compared and sorted. Each sql server instance has a default collation. Furthermore, each 'string' column (char, varchar, nchar, nvarchar, text, and ntext) has a collation associated with it.

Some collations are case sensitive, some are not. Some collations will treat accent marks the same as non-accent marks.

If 'DATA' Collate SQL_Latin1_General_CP1_CI_AS = 'Data' Collate SQL_Latin1_General_CP1_CI_AS
	Select 'They are equal'
Else
	Select 'They are not equal'

If 'DATA' Collate Latin1_General_Bin = 'Data' Collate Latin1_General_Bin
	Select 'They are equal'
Else 
	Select 'They are not equal'

Take a look at the previous code block. SQL_Latin1_General_CP1_CI_AS is a case insensitive collation. Latin1_General_Bin is a case sensitive collation.

Alternatively, you could convert the data to a binary format for comparison.

If Convert(Varbinary, 'DATA') = Convert(VarBinary, 'Data')
	Select 'They are equal'
Else 
	Select 'They are not equal'

-George

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