Technical Article

Proper Case the data in the Table


Persons Names,Addresses or any other master information if not entered with proper case . I mean if some records are with All caps ,some with all small or any other combination.You may be able to convert this data to proper case. Which will help you to show it in better looks on websites or in the reports.
You give two parameters "table name" and "column name of same table" of which to want to proper case the data

--@@@--Written By Vidyadhar Pandekar 
--@@@--C-DAC Pune
--@@@--Date 17/10/2003
--Name: Proper Case Conversion
--Functionality :
--"Execute pr_propcase 'table name','column name'"
--  Replaces all data of the specified column of 
--specified table by proper case formated Data
CREATE procedure pr_propcase @tab varchar (50),@valcol varchar (50)
declare @tab_n varchar (50)
declare @valcol_n varchar (50) 
declare @s_query nvarchar (500)
declare @u_query nvarchar (500)
declare @i int
select @i=1
declare @len int
declare @Enm varchar (2000)
declare @Enm1 varchar (2000)
declare @Dum varchar (2000)
Select @tab_n= @tab
Select @valcol_n= @valcol

select @s_query='select '+ @valcol_n +' from '+ @tab_n

create table #temptab (emp_name varchar (1000) )
insert #temptab exec sp_executesql  @s_query

declare cur1 cursor
for select emp_name from #temptab
open cur1
Fetch next from cur1 into @Enm

while @@FETCH_STATUS =0
begin  --*Begin-1
select @Enm1 = @Enm
select @Enm = ltrim (rtrim (@Enm))
select @len = len (@Enm)
select @i=1
while (@i<=@len)

    begin --*Begin-2

----Converts first character (if between a-z ) to Upper Case--------

if ((@i=1) and (ASCII(substring (@Enm, 1,1)) between 97 and 122))

 select @Dum = CHAR (ASCII(substring(@Enm,1,1))-32) 
  end --End*-3

if ((@i=1) and (ASCII(substring (@Enm, 1,1))   
not between 96 and 123))
begin --*Begin-4
select @Dum = substring(@Enm,1,1) 
 end --End*-4

---------Converts other than first character to Lower Case-----------

if (@i>1) and (ASCII(substring (@Enm, @i,1))between 65 and 90)
  begin --*Begin-5
select @Dum = @Dum + CHAR (ASCII(substring(@Enm,@i,1))+ 32)
if (@i>1) and (ASCII(substring (@Enm, @i,1))between 97 and 123)
  begin  --*Begin-6
select @Dum = @Dum + substring (@Enm, @i,1) 
if (@i>1) and (ASCII(substring (@Enm, @i,1))<65 or 
      ASCII(substring (@Enm, @i,1))>122) or 
      (ASCII(substring (@Enm, @i,1))>90 and 
      ASCII(substring (@Enm, @i,1))<97)
  begin --*Begin-7
select @Dum = @Dum + substring (@Enm, @i,1) 


--------Converts any charactor (between a-z) followed by any special character
--------to Upper Case----------------------------------------------------

if ( ((ascii (substring(@dum,@i-1,1)) between 1 and 64 ) or 
              (ascii (substring(@dum,@i-1,1)) between 91 and 96 ) or
     (ascii (substring(@dum,@i-1,1)) >122 ))and 
      ASCII(substring(@dum,@i,1))between 97 and 122)
select @Dum = Replace (@Dum,(substring(@Dum,@i-1,1)+substring(@Dum,@i,1)),
end --End*-8

-------Keep II as it is --------------------------------------------------------------------------------------

if ( (@i>1) and (substring(@Enm,@i-1,1)='I')  and  (substring(@Enm,@i,1)='I' or substring(@Enm,@i,1)='i' ) ) 
if (substring(@Enm,@i,1)='i')
select @Dum = Replace (@Dum,(substring(@Dum,@i-1,1)+substring(@Dum,@i,1)),
select @Dum = Replace (@Dum,(substring(@Dum,@i-1,1)+substring(@Dum,@i,1)),
end --End*-9


Select @i=@i+1 


select @u_query= 'update '+ @tab_n +' set '+@valcol_n+' = ''' + @dum +
''' where '+@valcol_n+ ' = ''' +  @Enm1 + ''''

exec sp_executesql @u_query
select @Dum=''
Fetch Next from cur1 into @Enm 
drop table #temptab
Close cur1
Deallocate cur1