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