Technical Article

Proper Case User Defined Function

,

This is a little script I wrote before I saw Steve Jones write up on Tame Those Strings Part 7. This is a slightly diffent approach. It will take a string up to 255 chars and case every word first letter upper and the rest of the word lower. This ain't the best way but it was quick and dirty. We use this script to clean up addresses but it should work for names as long as they are not McNeal or some other type of double case words.

useage:

select dbo.udf_proper_case() as  from

CREATE FUNCTION udf_proper_case (@STRIN nvarchar(255))
/**********************************************************************
udf_proper_case

by: Wesley D. Brown
Date 02/20/03

This is a user defined function that will change the 
case of any variable or column to proper mixed upper
and lower case

This UDF is designed to take one parameter
@STRIN::This will take a string 255 characters long

This UDF is designed to return one variable
@output::This will return a string up to 255 characters

This has only been tested under MS-SQL2k and Win2k
***********************************************************************/RETURNS nvarchar(255)
AS
BEGIN
--declare all variables needed to hold and return values
declare @a as varchar(255)
--@a holds a string part
declare @b as varchar(255)
--@b holds a string part
declare @c as varchar(255)
--@c holds a string part
declare @spc as int
--@spc holds the place a space is detected
declare @str as varchar(255)
--@str holds a string part
declare @output as varchar(255)
--@output holds temporary results as well as
--the final output
set @output =''
--set the output to a valid string
set @str = @STRIN
--set the @str holder to the incoming
--variable 
while CHARINDEX (' ',@str) > 0
--start while loop as long as CHARINDEX finds
--a blank space parse the variable
begin
set @spc = CHARINDEX (' ',@str)
--set the holder so we know what
--position the space is at
set @a = Lower(substring( @str, 1, @spc - 1))
--pull off the first section and lower case 
--the string
set @b = Upper(substring(@a,1,1))
--pull off the first letter and upper case
--the letter
set @c = @b+substring(@a, 2, len(@a))
--join the upper case letter to the 
--lower case body
set @str = substring( @str, @spc + 1, len( @str))
--trim off the parsed part of the string and just
--leave the raw string left
set @output = @output + space(1)+@c
--put the clean string in the output holder
end 
begin
set @a = Lower(@str)
--lower the remaining part of the string
set @b = Upper(substring(@a,1,1))
--upper the first letter in the string
set @c = @b + substring(@a, 2, len(@a))
--join the two together 
set @output = @output + space(1)+@c
--place it into the output holder
set @output = ltrim(@output)
--trim up any spaces on the front of the 
--string
end 

RETURN(@output)
--return the output
END

Rate

Share

Share

Rate