Replacing a character field with "Title", "Proper" , "Initcap" function

take a look at theis function

http://vyaskn.tripod.com/code/propercase.txt

update
YourTable set
 site_name = Upper(site_name) +'  -'

running this code got following error message

update
tblsite set
 oldsitename = Upper(oldsitename) +'  -'


Server: Msg 8152, Level 16, State 6, Line 1
String or binary data would be truncated.
The statement has been terminated.

this is because when you are appnedin '  -' the length of the string becomes more than the lenght of field itselp
what is the length of field oldsitename?
Then length of Upper(oldsitename) +' -'should be less than or equal to length of oldsitename field.

http://www.windowsitpro.com/Article/ArticleID/14117/14117.html

found this procedure on the web and it does the job - however how would i change it to update a column in a table

eg

update tblsite
set site_name=INITCAP(site_name) then call this site procedure

execute initcap

ie execute initcap stored procedure to change a field in a table????

CREATE PROCEDURE [InitCap]
@StrStr varchar(50)

AS

BEGIN
DECLARE @StrNew varchar(50)
DECLARE @StrCurrent     varchar(1)
DECLARE @StrPrevious    varchar(1)
DECLARE @x              integer
DECLARE @StrLen         integer
DECLARE @CloseBracket   varchar(5)
DECLARE @OpenBracket    varchar(5)

SELECT  @StrPrevious = LEFT(@StrStr,1),@StrNew = ' ',@x = 1, @StrLen = LEN(@StrStr)+1
SELECT @OpenBracket = CHAR(34) + CHAR(39) + CHAR(40) + CHAR(91) + CHAR(123)
SELECT @CloseBracket = CHAR(34) + CHAR(39) + CHAR(41) + CHAR(93) + CHAR(125)

WHILE @x < @StrLen
        BEGIN
        SET @StrCurrent = SUBSTRING(@StrStr,@x,1)
        IF @x = 1 AND @StrCurrent <> ' ' SET @StrNew = @StrNew + UPPER(@StrCurrent)
        ELSE BEGIN
                IF (@StrPrevious = ' ' AND @StrCurrent <> ' ')
                         SET @StrNew = @StrNew + UPPER(@StrCurrent)
                ELSE IF CHARINDEX(@StrPrevious,@OpenBracket) <> 0
                BEGIN
                        SET @StrNew = @StrNew + @StrCurrent
                        SET @x = @x +1
                        SET @StrCurrent = SUBSTRING(@StrStr,@x,1)
                        WHILE CHARINDEX(@StrCurrent,@CloseBracket) = 0
                        BEGIN
                                SET @StrNew = @StrNew + @StrCurrent
                                SET @x = @x +1
                                SET @StrCurrent = SUBSTRING(@StrStr,@x,1)
                        END
                        SET @StrNew = @StrNew + @StrCurrent
                END
                ELSE SET @StrNew = @StrNew + LOWER(@StrCurrent)
        END
        SET  @StrPrevious = @StrCurrent
        SET @x = @x +1
END
PRINT @StrNew
END

GO

--Sorry for the previous post
--==================


--Create this function and then use the update statement
update yourtable set site_name=dbo.Change_format(site_name)


--Function
--=====
Create function DBO.CHANGE_FORMAT (@STRING AS NVARCHAR(500))
RETURNS
NVARCHAR(500)
AS
BEGIN
declare
      @index as int,
      @ptr as int,
      @substr as Nvarchar(500),
      @newstr as Nvarchar(500)

set @newstr =''
set @substr =''
set @index = 0
while @index <= len (@string)
      begin
      set @ptr = 1
            while ((substring(@string,@index+@ptr,1) not in (' ',',','.')) and (@index+@ptr < = len (@string)))
                  begin
                        Set @substr = @substr + substring(@string,@index+@ptr,1)
                        set @ptr = @ptr + 1
                  end
            if (@substr not in ('is','of','on','for','can','not','and','are','or'))
            set @substr = upper(left(@substr,1))+ substring(lower(@substr),2,len(@substr))
            set @newstr = @newstr + @substr + substring(@string,@index+@ptr,1)
            set @index = @index + @ptr
            set @substr =''
      end
RETURN @newstr
END
GO

genius !!!! this works perfectly

thanks

chris

>>this works perfectly<<
Great!  Now please close the question.