Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-03-24
10
Medium Priority
?
387 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
0
Comment
Question by:Chris Michalczuk
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16287226
take a look at theis function

http://vyaskn.tripod.com/code/propercase.txt
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16287236
update
YourTable set
 site_name = Upper(site_name) +'  -'
0
 

Author Comment

by:Chris Michalczuk
ID: 16287362
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:imran_fast
ID: 16287518
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16287653
0
 

Author Comment

by:Chris Michalczuk
ID: 16287894

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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16288077
--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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 600 total points
ID: 16288085
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
 

Author Comment

by:Chris Michalczuk
ID: 16288125
genius !!!! this works perfectly

thanks

chris
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16289505
>>this works perfectly<<
Great!  Now please close the question.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question