Technical Article

Case Cleanup Function

,

Simply pass a parameter to the function, such as "Company Name":

SELECT
[dbo].[ufn_Case_Cleanup] (C.company_name) AS case_cleanup _company_name
FROM

company_table C

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON


----------------------------------------------------------------------------------------------------------------------
--Error Trapping: Check If Function Already Exists And Drop If Applicable
----------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID ('[dbo].[ufn_Case_Cleanup]') IS NOT NULL
BEGIN

DROP FUNCTION [dbo].[ufn_Case_Cleanup]

END
GO


----------------------------------------------------------------------------------------------------------------------
--Function Details: Listing Of Standard Details Related To The Function
----------------------------------------------------------------------------------------------------------------------

-- Purpose: Cleanup Letter Casing Of Each Word In A String
-- Create Date: 08/08/2008
-- Created By: Sean Smith (s(DOT)smith(DOT)sql(AT)gmail(DOT)com)
-- Modifications: NONE


----------------------------------------------------------------------------------------------------------------------
--Main Query: Create Function
----------------------------------------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[ufn_Case_Cleanup] (@vParameter AS VARCHAR (8000))

RETURNS VARCHAR (8000)

AS

BEGIN

DECLARE @vParameter_Length AS INT
DECLARE @vPosition_Current AS INT
DECLARE @vCharacter_Current AS VARCHAR (1)


SET @vParameter = LOWER (@vParameter)
SET @vParameter_Length = ISNULL (LEN (@vParameter),0)


IF @vParameter_Length = 0
BEGIN

GOTO skip_query

END


SET @vParameter = STUFF (@vParameter, 1, 1, UPPER (LEFT (@vParameter,1)))
SET @vPosition_Current = 1
SET @vCharacter_Current = UPPER (LEFT (@vParameter,1))


WHILE @vPosition_Current < @vParameter_Length
BEGIN

IF @vCharacter_Current IN (' ', '!', '"', '&', '(', '*', '+', ',', '-', '.', '/', ':', ';', '\')
BEGIN

SET @vParameter = STUFF (@vParameter, @vPosition_Current+1, 1, UPPER (SUBSTRING (@vParameter, @vPosition_Current+1, 1)))

END


SET @vPosition_Current = @vPosition_Current+1
SET @vCharacter_Current = SUBSTRING (@vParameter, @vPosition_Current, 1)

END


skip_query:


RETURN @vParameter

END

Rate

5 (10)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (10)

You rated this post out of 5. Change rating