Today, I came across the situation where we need to update the text column and we want all the column text to UPPER CASE. But, we do not want to update the rows for which this text column already contains the word in upper case.
use AdventureWorks
GO
if object_id('tblCheckCase') is not null
drop table tblCheckCase
GO
Create table tblCheckCase
(
id int identity(1,1) primary key,
firstName nvarchar(10)
)
GO
insert into tblCheckCase(firstName)
select 'chintak'
union all
select 'chintan'
union all
select 'dhyey'
union all
select 'VIKAS'
union all
select 'DATTU'
GO
select firstName,charindex( firstName collate SQL_Latin1_General_Cp1_CS_AS,upper(firstName)) from tblCheckCase
GO
update tblCheckCase
set firstName = UPPER(firstName)
where charindex( firstName collate SQL_Latin1_General_Cp1_CS_AS,upper(firstName)) = 0
GO
select firstName as firstNameAfter,charindex( firstName collate SQL_Latin1_General_Cp1_CS_AS,upper(firstName)) from tblCheckCase
GO
Drop table tblCheckCase
GO
