Menu
How can I find column values that are in all caps? Like LastName = 'SMITH' instead of 'Smith'
Here is what I was trying...
SELECT * FROM MyTable WHERE FirstName = UPPER(FirstName) 
709
Example 2: Use UPPER function with all lower case characters in an expression. In this example, we use a string with Upper function, and it converts all letters in the string to uppercase. SELECT UPPER('learn sql server with sqlshack'); Output: It converts all characters for a string.
The UPPER() function converts a string to upper-case. Note: Also look at the LOWER() function.
Case insensitive SQL SELECT: Use upper or lower functions or this: 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.
The SQL LOWER function converts all the characters in a string into lowercase. If you want to convert all characters in a string into uppercase, you should use the UPPER function. The following illustrates the syntax of the LOWER function. The LOWER function returns a string with all characters in the lowercase format.
You can force case sensitive collation;
select * from T where fld = upper(fld) collate SQL_Latin1_General_CP1_CS_AS Try
SELECT * FROM MyTable WHERE FirstName = UPPER(FirstName) COLLATE SQL_Latin1_General_CP1_CS_AS This collation allows case sensitive comparisons.
If you want to change the collation of your database so you don't need to specifiy a case-sensitive collation in your queries you need to do the following (from MSDN):
1) Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
2) Export all your data using a tool such as the bcp Utility.
3) Drop all the user databases.
4) Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] /SQLCOLLATION=CollationName 5) Create all the databases and all the objects in them.
6) Import all your data.
39
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
