SQL 2k Propercase Function 1

[Glowworm27]

Found this usefull

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/************************************** Proper Case Function *************************************************
*	This function will accept a Varchar up to 2000 chars long (or modify the input definition to suit your needs)
*	And return a Varchar up to 2000 chars long, that has been converted to proper case. The Function assumes that a
*	Single space, a comma, or a dash (-) delimit new words, and will capitalize the the first letter in each word.
*
*	Inputs : up to 2000 Varchar characters
*	Returns: up to 2000 Varchar Characters properly cased ( first letter of each word is Upper Case the rest are lower case
*	Usage:    Select dbo.ProperCase('THIS is A ReaLY bAD EXamPLE OF IMprOPER CASE')
*	Returned: This Is A Realy Bad Example Of Improper Case
*/

ALTER   FUNCTION dbo.ProperCase
(
--The string to be converted to proper case
@input VARCHAR(2000)
--The character(s) to be recognized as separating strings to be capitalized

)

RETURNS VARCHAR(2000)
AS
BEGIN

DECLARE @byte SMALLINT
DECLARE @delimFound BIT
DECLARE @delims VARCHAR(10) 

SET @byte = 1
SET @delimFound = 1

SET @delims = ' ,-'

WHILE @byte <= LEN(@input)
	BEGIN
		IF CHARINDEX(SUBSTRING(@input, @byte, 1), @delims) > 0
			SET @delimFound = 1
		ELSE
		BEGIN
			IF @delimFound = 1
			BEGIN
				SET @input = STUFF(@input, @byte, 1, UPPER(SUBSTRING(@input, @byte, 1)))
			SET @delimFound = 0
			END --IF
		ELSE
		SET @input = STUFF(@input, @byte, 1, LOWER(SUBSTRING(@input, @byte, 1)))
	END --ELSE
		SET @byte = @byte + 1
	END --WHILE
	RETURN @input

END --FUNCTION



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[ESquared]

It's a decent function, although it's obviously been modified (an input parameter has been hardcoded).

But, I can't resist... just set all the letters to lower at the start and you can lose a whole ELSE clause that had to run for each lower case letter.

Also, since "SUBSTRING(@input, @byte, 1)" can be evaluated more than once each loop, it might be good to put it in a variable and then refer to the variable so it's done only once. (I'm *always* looking for optimizations ). I hope I haven't been Too Annoying With This Post.

ALTER FUNCTION dbo.ProperCase(
   @input VARCHAR(2000)--The string to be converted to proper case
   )

RETURNS VARCHAR(2000)
AS
BEGIN

   DECLARE @byte SMALLINT
   DECLARE @delimFound BIT
   DECLARE @delims VARCHAR(10) 
   
   SET @byte = 1
   SET @delimFound = 1
   SET @delims = ' ,-'

   [b]SET @input = LOWER(@input)[/b]

   WHILE @byte <= LEN(@input) BEGIN
      IF CHARINDEX(SUBSTRING(@input, @byte, 1), @delims) > 0
         SET @delimFound = 1
      ELSE IF @delimFound = 1 BEGIN
         SET @input = STUFF(@input, @byte, 1, UPPER(SUBSTRING(@input, @byte, 1)))
         SET @delimFound = 0
      END [COLOR=008080]-- trimmed else clause[/color]
      SET @byte = @byte + 1
   END
   RETURN @input

END

 

[Glowworm27]

Hey ESquared

Gave you a Star on that one. I am always looking to improve code as well, and that didn't even dawn on me until you mentioned it. Good Catch!

[Cannon]

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Glowworm27]

Oh btw, yes I hard-coded the @Delims becasue for the most part the space, Comma and dash are the usual new word separators, and I didn't want to keep entering them into the select statement everytime I wanted to use the propercase function

G

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[SQLSister]

George, You might consider that / also separates words frequently as in his/her.

Questions about posting. See faq183-874

 

[Glowworm27]

SQLSister,

Man I must be asleep today. Good Catch on that one too....

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[ESquared]

Thanks!

If we're really going for optimization, a rewrite of the function might search for the delimiters one at a time, instead of checking each character in the string to see if it is one of the delimiters. Let's say the ratio of delimiters to characters is 1:5. With the method as listed above, it has to do 6 times the number of searches.

Doing a search one at a time for each delimiter would be 3 outer loops, but one-sixth the number of 'inner' loops.

A second rewrite would make the function handle the following properly:

don't say 'goodbye' (capitalize g but not t)

And probably some other exceptions I can't think of right now... hehe

 

[ESquared]

I didn't realize you had written it. Good work.

You might have noticed I changed the format of ENDs and BEGINs and things in my version. Since you had lots of comments indicating the block start that each block end corresponded with, you might like to check out the threads Stored Procedure structure and Recommendations for MSSQL coding conventions!

With the format donutman and I have argued out, it's very clear which block end belongs to which block start.