Technical Article

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