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
--E-mail: vidya_pande@yahoo.com
--@@@--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)
as
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))
begin--*Begin-3
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)
end--End*-5
if (@i>1) and (ASCII(substring (@Enm, @i,1))between 97 and 123)
begin --*Begin-6
select @Dum = @Dum + substring (@Enm, @i,1)
end--End*-6
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)
end--End*-7
---------------------------------------------------------------------
--------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)
begin--*Begin-8
select @Dum = Replace (@Dum,(substring(@Dum,@i-1,1)+substring(@Dum,@i,1)),
(substring(@Dum,@i-1,1)+CHAR(ASCII(substring(@Dum,@i,1))-32)))
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' ) )
begin--*Begin-9
if (substring(@Enm,@i,1)='i')
select @Dum = Replace (@Dum,(substring(@Dum,@i-1,1)+substring(@Dum,@i,1)),
(substring(@Dum,@i-1,1)+CHAR(ASCII(substring(@Dum,@i,1))-32)))
else
select @Dum = Replace (@Dum,(substring(@Dum,@i-1,1)+substring(@Dum,@i,1)),
(substring(@Dum,@i-1,1)+CHAR(ASCII(substring(@Dum,@i,1)))))
end --End*-9
-------------------------------------------------------------------------------------------------------------------------
Select @i=@i+1
end--End*-2
------------------------------------------------------------------------
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
End--End*-1
drop table #temptab
Close cur1
Deallocate cur1
----------------------------------End-------------------------------------------
GO 
