We help IT Professionals succeed at work.

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

Medium Priority
409 Views
Last Modified: 2010-08-05
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
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
take a look at theis function

http://vyaskn.tripod.com/code/propercase.txt
Top Expert 2006

Commented:
update
YourTable set
 site_name = Upper(site_name) +'  -'
Chris MichalczukConsultant

Author

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.
Top Expert 2006

Commented:
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.
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
Chris MichalczukConsultant

Author

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
Top Expert 2006

Commented:
--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
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
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)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Chris MichalczukConsultant

Author

Commented:
genius !!!! this works perfectly

thanks

chris
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>this works perfectly<<
Great!  Now please close the question.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.