Quick Propercase Function

,

This is my attempt to improve the performance of propercasing a string of text in SQL server. I have run this against several other "looping constructs" - and it is significantly faster.

/*************************************************************************
	Author:  Richard Ozenbaugh (richardoz@xerisoft)
	Date:    09/24/2005
	
	Notes:   Propercase function 

*************************************************************************/

-- THIS IS A WORKING TABLE FOR USE WITH THE FN_PROPERCASE
CREATE TABLE MY_NUMBERS ( NUMBER INT, CONSTRAINT PK_MY_NUMBERS PRIMARY KEY  CLUSTERED ( NUMBER ) )
GO

-- POPULATE THE WOKRING TABLE WITH NUMBERS 1 TO 8000
DECLARE @COUNTER INT
SET NOCOUNT ON
SET @COUNTER = 1
WHILE @COUNTER < 8001
BEGIN
	INSERT INTO MY_NUMBERS ( NUMBER ) VALUES(@COUNTER)
	SET @COUNTER = @COUNTER + 1
END
SET NOCOUNT OFF
GO

-- CHECK..  IF GUESS IF YOUR'S PARANOID
SELECT [NUMBER] FROM [PFE_LANDING_DEV2].[dbo].[MY_NUMBERS]
GO

-- CREATE THE PROPERCASE FUNCTION
CREATE FUNCTION DBO.FN_PROPERCASE(@STRING VARCHAR(8000)) RETURNS VARCHAR(8000) AS
BEGIN
	DECLARE @PROPER VARCHAR(8000)
	SET @PROPER = ''
	SELECT
		@PROPER = @PROPER + 
		CASE
			WHEN NUMBER = 1 THEN UPPER(SUBSTRING(@STRING, NUMBER, 1))
			WHEN NUMBER > 1 AND SUBSTRING(@STRING, NUMBER -1, 1) = ' ' THEN UPPER(SUBSTRING(@STRING, NUMBER, 1))
			ELSE LOWER(SUBSTRING(@STRING, NUMBER, 1))
		END
	FROM
		MY_NUMBERS
	WHERE
		NUMBER <= LEN(@STRING)
	RETURN @PROPER
	-- USEAGE:
	-- SELECT dbo.FN_PROPERCASE('COMPANY NAME IN ALL CAPS')
END
GO

Rate

Share

Share

Rate