Update Column In Database For A Table Having Upper Case Value

Update all the upper case values to the lower case, using SQL query. You can use this for large updates in the table also.

Create database

Here, we have created a database named Test_DB and you can create it with a different name also.

After creating the database, there is a need to create a table in which we put some records.

Create a test table

I have created a table with TestTable name, having two columns named ID and EmailAddress, where ID is the primary key.
The image, given below, is the design of our table:



Now we have to insert some records in this table, and run the below script :

Insert value in table
Run the script, given above.

See below screen



Now, we have to check the inserted record, using select query.

We found the retrieved record from the command, given below:


In the table, we see some records are in the upper case and some are in the lower case.

We need to update all the upper case values to the lower case. For this, we need first to know about the exact value for the upper case , which is to be update in the lower case.

How do we know if the records are in the upper case or in the lower case?

We can find these records with many queries but I am using some here:

1. Select * from test_up where ASCII(left(EmailAddress, 1)) between ASCII('A')
   and ASCII('Z')
   
   
   
   
2. Select * from Testtable where EmailAddress!=upper(emailaddress)COLLATE Latin1_General_CS_AS
   
   
   
   
3. Using function in SQL
   
   Create a function,
   
   1. create function dbo.fnIsStringInAllUppercase(@input nvarchar(max)) returns bit
   3. as
   5. begin
   7. if (ISNUMERIC(@input) = 0 AND RTRIM(LTRIM(@input)) > '' AND @input = UPPER(@input COLLATE Latin1_General_CS_AS))
   8. return 1;
   10. return 0;
   11. end
   Now check value using Function :
   
   in the select query,
   
   1. SELECT *
   2. FROM Testtable
   3. WHERE dbo.fnIsStringAllUppercase(EmailAddress) = 1
   From the code, given above, you can see your data, which is required to be updated.
   
   Use the query, given below:
   
   1. BEGIN TRANSACTION
   2. GO
   4. UPDATE [dbo].[test_up]
   5. SET EmailAddress = lower(EmailAddress)
   6. WHERE id in(select id from test_up where EmailAddress!=lower(emailaddress)COLLATE Latin1_General_CS_AS )
   7. GO
   COMMIT TRANSACTION,
   
   
   Now, we can see the table records:
   
   
   
   All the upper case values will convert in the lower case values.