function to do initial capitals? 2

[anorakgirl]

I have loaded some data into a table from a spreadsheet. The problem is, in the spreadsheet it was ALL CAPITALS. It'd really like to update it in the database to Initial Capitals. I can't see a SQL Server function for doing this, and wondered if it would be possible to do with a stored procedure? Has anyone done this who could point me in the right direction?
Thanks!
ag

~

http://www.anorakgirl.co.uk

~

 

[dbomrrsm]

Here you go:

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

[blue]DBomrrsm[/blue]

 

[TysonLPrice]

Just a variation. I can't take credit for it. Found it free somewhere:

CREATE FUNCTION fnc_ProperCase
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
    IF @input IS NULL 
    BEGIN
        --Just return NULL if input string is NULL
        RETURN NULL
    END
    
    --Character variable declarations
    DECLARE @output varchar(8000)
    --Integer variable declarations
    DECLARE @ctr int, @len int, @found_at int
    --Constant declarations
    DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int
    
    --Variable/Constant initializations
    SET @ctr = 1
    SET @len = LEN(@input)
    SET @output = ''
    SET @LOWER_CASE_a = 97
    SET @LOWER_CASE_z = 122
    SET @Delimiter = ' ,-'
    SET @UPPER_CASE_A = 65
    SET @UPPER_CASE_Z = 90
    
    WHILE @ctr <= @len
    BEGIN
        --This loop will take care of reccuring white spaces
        WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
        BEGIN
            SET @output = @output + SUBSTRING(@input,@ctr,1)
            SET @ctr = @ctr + 1
        END

        IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
        BEGIN
            --Converting the first character to upper case
            SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
        END
        ELSE
        BEGIN
            SET @output = @output + SUBSTRING(@input,@ctr,1)
        END
        
        SET @ctr = @ctr + 1

        WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
        BEGIN
            IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
            BEGIN
                SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
            END
            ELSE
            BEGIN
                SET @output = @output + SUBSTRING(@input,@ctr,1)
            END
            SET @ctr = @ctr + 1
        END
        
    END
RETURN @output
END

 

[TysonLPrice]

Just couldn't stop myself. Here's another variation. It does a column in a table:

I get alot of these things from here:

http://www.sqlservercentral.com/Scripts/listscripts.asp?categorynm=T-SQL Aids&categoryid=19

Persons Names,Addresses or any other master information if not entered with proper case . I mean if some records are with All caps ,some with all small or any other combination.You may be able to convert this data to proper case. Which will help you to show it in better looks on websites or in the reports.
You give two parameters "table name" and "column name of same table" of which to want to proper case the data

--------------------------------------------------------------------------
--@@@--Written By Vidyadhar Pandekar 
--@@@--C-DAC Pune
--E-mail: vidya_pande@yahoo.com
--@@@--Date 17/10/2003
--Name: Proper Case Conversion
--Functionality :	
--	"Execute pr_propcase 'table name','column name'"
--  	Replaces all data of the specified column of 
--	specified table by proper case formated Data
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
CREATE procedure pr_propcase @tab varchar (50),@valcol varchar (50)
as 
declare @tab_n varchar (50)
declare @valcol_n varchar (50) 
declare @s_query nvarchar (500)
declare @u_query nvarchar (500)
declare @i int
select @i=1
declare @len int
declare @Enm varchar (2000)
declare @Enm1 varchar (2000)
declare @Dum varchar (2000)
Select @tab_n= @tab
Select @valcol_n= @valcol

select @s_query='select '+ @valcol_n +' from '+ @tab_n

create table #temptab (emp_name varchar (1000) )
insert #temptab exec sp_executesql  @s_query

declare cur1 cursor
for select emp_name from #temptab
open cur1
Fetch next from cur1 into @Enm

while @@FETCH_STATUS =0
begin  								--*Begin-1
	select @Enm1 = @Enm
	select @Enm = ltrim (rtrim (@Enm))
	select @len = len (@Enm)
	select @i=1
	while (@i<=@len)

	    begin 						--*Begin-2

	----Converts first character (if between a-z ) to Upper Case--------

		if ((@i=1) and (ASCII(substring (@Enm, 1,1)) between 97 and 122))

		  begin						--*Begin-3
		 	select @Dum = CHAR (ASCII(substring(@Enm,1,1))-32) 
		  end 						--End*-3
		
				if ((@i=1) and (ASCII(substring (@Enm, 1,1))   
						not between 96 and 123))
		begin 						--*Begin-4
			select @Dum = substring(@Enm,1,1) 
		 end 						--End*-4

	---------------------------------------------------------------------
	---------Converts other than first character to Lower Case-----------

		if (@i>1) and (ASCII(substring (@Enm, @i,1))between 65 and 90)
		  begin 					--*Begin-5
				select @Dum = @Dum + CHAR (ASCII(substring(@Enm,@i,1))+ 32)
		  end						--End*-5
		if (@i>1) and (ASCII(substring (@Enm, @i,1))between 97 and 123)
		  begin  					--*Begin-6
				select @Dum = @Dum + substring (@Enm, @i,1) 
		  end						--End*-6
		if (@i>1) and (ASCII(substring (@Enm, @i,1))<65 or 
			      ASCII(substring (@Enm, @i,1))>122) or 
			      (ASCII(substring (@Enm, @i,1))>90 and 
			      ASCII(substring (@Enm, @i,1))<97)
		  begin 					--*Begin-7
				select @Dum = @Dum + substring (@Enm, @i,1) 
		  end						--End*-7

 	---------------------------------------------------------------------

							

	--------Converts any charactor (between a-z) followed by any special character
	--------to Upper Case----------------------------------------------------

		if ( ((ascii (substring(@dum,@i-1,1)) between 1 and 64 ) or 
         		     (ascii (substring(@dum,@i-1,1)) between 91 and 96 ) or
			     (ascii (substring(@dum,@i-1,1)) >122 ))and 
			      ASCII(substring(@dum,@i,1))between 97 and 122)
			begin					--*Begin-8
			select @Dum = Replace (@Dum,(substring(@Dum,@i-1,1)+substring(@Dum,@i,1)),
				(substring(@Dum,@i-1,1)+CHAR(ASCII(substring(@Dum,@i,1))-32)))
			end 					--End*-8
		
	-------------------------------------------------------------------------------------------------------------------
	-------Keep II as it is --------------------------------------------------------------------------------------
		
		if ( (@i>1) and (substring(@Enm,@i-1,1)='I')  and  (substring(@Enm,@i,1)='I' or substring(@Enm,@i,1)='i' ) ) 
         		     
			begin					--*Begin-9
				if (substring(@Enm,@i,1)='i')
					select @Dum = Replace (@Dum,(substring(@Dum,@i-1,1)+substring(@Dum,@i,1)),
					(substring(@Dum,@i-1,1)+CHAR(ASCII(substring(@Dum,@i,1))-32)))
				else
					select @Dum = Replace (@Dum,(substring(@Dum,@i-1,1)+substring(@Dum,@i,1)),
					(substring(@Dum,@i-1,1)+CHAR(ASCII(substring(@Dum,@i,1)))))
			end 					--End*-9
	
	-------------------------------------------------------------------------------------------------------------------------

			Select @i=@i+1 
	    end							--End*-2
		  					

	------------------------------------------------------------------------

select @u_query= 'update '+ @tab_n +' set '+@valcol_n+' = ''' + @dum +
''' where '+@valcol_n+ ' = ''' +  @Enm1 + ''''

exec sp_executesql @u_query
select @Dum=''
	Fetch Next from cur1 into @Enm 
End								--End*-1
drop table #temptab
Close cur1
Deallocate cur1

----------------------------------End-------------------------------------------
GO

 

[anorakgirl]

hey thanks both, i'll try those aout and thanks for the link TysonLPrice - I work with a few different databases but am fairly new to SQL Server so haven't yet got a list of good sites for things like this.
thanks again!

~

http://www.anorakgirl.co.uk

~

 

[ESquared]

This is a function I wrote, which I am particularly attached to because it is short and sweet. The other procedures are so long!

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InitCap]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[InitCap]
GO

CREATE FUNCTION InitCap(@TheString varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
   DECLARE @pos int
   SET @TheString = Lower(@TheString)
   SET @pos = 1
   WHILE @pos IS NOT NULL BEGIN
      SET @TheString = Stuff(@TheString,@pos,1,Upper(SubString(@TheString,@pos,1)))
	   SET @pos = @pos + NullIf(PatIndex('%[^a-z0-9][a-z]%',SubString(@TheString,@pos+1,8000)),0) + 1
   END
   RETURN @TheString
END

GO

PRINT dbo.InitCap('tHe cOW JumPED Over THE 12mOOn Forty-seven times.')

You can change the [^a-z0-9] part to indicate what you do or don't want to signify that the next character should be a capital. For example, you might leave out single quote characters. [^a-z0-9']

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.