Menu
I have a table that originates in an old legacy system that was case senstive, in particular a status column where 's' = 'Schedule import' and 'S' = 'Schedule management'. This table eventually makes its way into a SQL Server 2000 database which I can query against. My query is relatively simple just going for counts...
Select trans_type, count(1) from mytable group by trans_type
This is grouping the counts for 'S' along with the 's' counts. Is there any way to force a query to be cap sensitive? I have access to both SQL Server 2000 and 2005 environments to run this, however have limited admin capability on the server (so I can't set server attributes)... I guess I could move the data to my local and setup something on my local where I have full access to server options, but would prefer a tsql solution.
662
SQL Server is, by default, case insensitive; however, it is possible to create a case-sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine if a database or database object is to check its "COLLATION" property and look for "CI" or "CS" in the result.
To do a case-insensitive comparison, use the ILIKE keyword; e.g., column ILIKE 'aBc' and column ILIKE 'ABC' both return TRUE for 'abc' . In contrast, MySQL and MS SQL Server have case-insensitive behaviors by default. This means WHERE column = 'abc' returns TRUE for e.g., 'abc' , 'ABC' , or 'aBc' .
Case insensitive SQL SELECT: Use upper or lower functions select * from users where lower(first_name) = 'fred'; As you can see, the pattern is to make the field you're searching into uppercase or lowercase, and then make your search string also be uppercase or lowercase to match the SQL function you've used.
transaction_name is always case sensitive, even when the instance of SQL Server is not case sensitive.
select trans_type collate SQL_Latin1_General_CP1_CS_AS, count(*)
from mytable
group by trans_type collate SQL_Latin1_General_CP1_CS_AS
You can do this with =, like, and other operators as well. Note that you must modify the select list because you are no longer grouping by trans_type, you are now grouping by trans_type collate SQL_Latin1_General_CP1_CS_AS. Kind of a gotcha.
154
Can you introduce a trans_type_ascii column with the ascii value of the trans_type and group on that instead? Or any other column you can use (isUpperCase) to distinguish them.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
