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

I'm new to Sql 2000 and want to convert data in a field to Title case

field is Site_Name

I can do set site_name=upper(Site_name) ---- returns for example BLOGGS LIMITED
but I want this to end up as Bloggs Limited -

only way at present is to export the data to foxpro use a convert string proper(fieldname) and then go back and do an import and update!!!

seems a very long winded way around it
Chris MichalczukConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
take a look at theis function

http://vyaskn.tripod.com/code/propercase.txt
0
imran_fastCommented:
update
YourTable set
 site_name = Upper(site_name) +'  -'
0
Chris MichalczukConsultantAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

imran_fastCommented:
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.
0
Aneesh RetnakaranDatabase AdministratorCommented:
0
Chris MichalczukConsultantAuthor Commented:

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
0
imran_fastCommented:
--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
0
Aneesh RetnakaranDatabase AdministratorCommented:
You can create a function with thew same structure


CREATE Function [InitCap] (
@StrStr varchar(50) )
RETURNS 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
Return @StrNew
END


GO


UpDATE urTabele
SET urField =   dbo.InitCap(urField)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris MichalczukConsultantAuthor Commented:
genius !!!! this works perfectly

thanks

chris
0
Anthony PerkinsCommented:
>>this works perfectly<<
Great!  Now please close the question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.