Hi. I have a client database that

[MrMystery]

Hi.
I have a client database that has been input into by a few different people. Some have used upper case, some lower. I want to run an SQL script to change everything to Title case - i.e. JOE BLOGGS to become Joe Bloggs
I have a script that will do this for a pre-defined value of a variable;
declare @old_str AS varchar(255)
declare @new_str AS varchar(255)
declare @currchar_id AS int
declare @prevchar_id AS int
select @old_str='THE QUICK BROWN FOX JUMPED OVER THE LAZY DOG'
select @new_str=''
select @currchar_id=0
select @prevchar_id=0

select @currchar_id=0
select @prevchar_id=0

while(@currchar_id < len(@old_str))
begin
select @currchar_id = @currchar_id + 1
select @new_str =
case (substring(@old_str,@prevchar_id,1))
when char(32) then (@new_str+upper(substring(@old_str,@currchar_id,1)))
else (@new_str+lower(substring(@old_str,@currchar_id,1)))
end
select @prevchar_id = @prevchar_id + 1
end

select @new_str

Can someone tell me how I can use this to update the contents of a field throughout an entire table ?

 

[SQLSister]

Try this. First create a function, then use the function in the update statement.
Function I use is:
Create function fn_mCase2
(
@my_str as varchar(8000)
)
Returns varchar (8000)
AS
Begin

Declare @this_str as varchar(8000)
Declare @word_str as varchar(5000)
Declare @spc int

Select @this_str = ''

Select
@my_str = LTrim(RTrim(@my_str))

While Len(@my_str) > 0
Begin

if (CharIndex(' ', @my_str) = 0)
Begin
Select
@spc = Len(@my_str)
End
Else
Begin
Select
@spc = CharIndex(' ', @my_str)
End

Select
@word_str = LTrim(RTrim(Left(@my_str, @spc))),
@my_str = LTrim(RTrim(Right(@my_str, (Len(@my_str)-@spc))))



Select
@this_str = RTrim(LTrim(@this_str + ' ' + (Upper(Left(@word_str,1)) + Lower(Right(@word_str,(Len(@word_str)-1))))))
End

Return (@this_str)
End


The update statement to update a field would then be:
Update table1
set Field1 = dbo.fn_mCase2(Field1)