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.

USE [master]
GO

/****** Object:  Database [Test_DB]    Script Date: 7/4/2016 7:14:32 AM ******/
CREATE DATABASE [Test_DB]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Test_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_DB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Test_DB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_DB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [Test_DB] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Test_DB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [Test_DB] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [Test_DB] SET ANSI_NULLS OFF
GO

ALTER DATABASE [Test_DB] SET ANSI_PADDING OFF
GO

ALTER DATABASE [Test_DB] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [Test_DB] SET ARITHABORT OFF
GO

ALTER DATABASE [Test_DB] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [Test_DB] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [Test_DB] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [Test_DB] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [Test_DB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [Test_DB] SET CURSOR_DEFAULT  GLOBAL
GO

ALTER DATABASE [Test_DB] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [Test_DB] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [Test_DB] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [Test_DB] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [Test_DB] SET  DISABLE_BROKER
GO

ALTER DATABASE [Test_DB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [Test_DB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [Test_DB] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [Test_DB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [Test_DB] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [Test_DB] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [Test_DB] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [Test_DB] SET RECOVERY SIMPLE
GO

ALTER DATABASE [Test_DB] SET  MULTI_USER
GO

ALTER DATABASE [Test_DB] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [Test_DB] SET DB_CHAINING OFF
GO

ALTER DATABASE [Test_DB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO

ALTER DATABASE [Test_DB] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO

ALTER DATABASE [Test_DB] SET  READ_WRITE
GO

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.

USE [Test_DB]
GO

/****** Object:  Table [dbo].[TestTable]    Script Date: 7/4/2016 7:13:39 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TestTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EmailAddress] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

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

insert into TestTable(EmailAddress) values('upe**.**@***.com')
insert into TestTable(EmailAddress) values('Upe**.**@***.com')
insert into TestTable(EmailAddress) values('uPe**.**@***.com')
insert into TestTable(EmailAddress) values('UPe**.**@***.com')
insert into TestTable(EmailAddress) values('SPe**.**@***.com')
insert into TestTable(EmailAddress) values('SPEe**.**@***.com')
insert into TestTable(EmailAddress) values('DSF**.**@***.com')
insert into TestTable(EmailAddress) values('FF**.**@***.com')
insert into TestTable(EmailAddress) values('FGF**.**@***.com')
insert into TestTable(EmailAddress) values('FF**.**@***.com')
insert into TestTable(EmailAddress) values('UTYU**.**@***.com')
insert into TestTable(EmailAddress) values('TYUYTU**.**@***.com')
insert into TestTable(EmailAddress) values('RAHUL**.**@***.com')
insert into TestTable(EmailAddress) values('RS**.**@***.com')
insert into TestTable(EmailAddress) values('rtt**.**@***.com')
insert into TestTable(EmailAddress) values('AWE**.**@***.com')
insert into TestTable(EmailAddress) values('RED**.**@***.com')

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:

select * from Testtable

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,
   

   create function dbo.fnIsStringInAllUppercase(@input nvarchar(max)) returns bit
   
       as
   
   begin
   
       if (ISNUMERIC(@input) = 0 AND RTRIM(LTRIM(@input)) > '' AND @input = UPPER(@input COLLATE Latin1_General_CS_AS))
           return 1;
   
       return 0;
   end

   Now check value using Function :
   
   in the select query,
   

   SELECT *
   FROM   Testtable
   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:
   

   BEGIN TRANSACTION
   GO
   
   UPDATE [dbo].[test_up]
   SET    EmailAddress  = lower(EmailAddress)
   WHERE id in(select id from test_up where EmailAddress!=lower(emailaddress)COLLATE Latin1_General_CS_AS )
   GO

   COMMIT TRANSACTION,
   
   
   Now, we can see the table records:
   
   
   
   All the upper case values will convert in the lower case values.