Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Simple T-SQL Proper Case User-Defined Function

I posted this one a long time ago and needed to use it today, so I thought I'd post it up here as well in case anyone finds it useful.  This simply attempts to capitalize the first letter of each word for the string passed in.  Use it to help clean up some pre-existing data, but don't use it as a way of presenting your data that is stored improperly since it isn't exact. 

For example, I have some data that stores press releases, and the headlines were entered all in capitals.  In an attempt to take the formatting out of the data, I will let the web page do the capitalization and use this function to reset the headlines to normal casing.  This gives the best flexibility, since now the data can be formatted either way and the client can decide; as it is now, stored in all capitals, the client has no choice but to display it that way. 

And you guys know how I hate mixing data and formatting!

create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
declare @Reset bit;
declare @Ret varchar(8000);
declare @i int;
declare @c char(1);

select @Reset = 1, @i=1, @Ret = '';

while (@i <= len(@Text))
select @c= substring(@Text,@i,1),
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
@i = @i +1
return @Ret
end

Example:

select dbo.ProperCase('this,my friends, is a test.wHat DO you think?i like shaquile o''neal')
——————————————————————-
This,My Friends, Is A Test.What Do You Think?I Like Shaquile O'Neal

(1 row(s) affected)

Legacy Comments


Jay (aka ehorn)
2007-03-10
re: Simple T-SQL Proper Case User-Defined Function
Hey Jeff, get rid of that loop :)

declare @text varchar(8000), @output varchar(8000)
select @text = 'this,my friends, is a test.wHat DO you think?i like shaquile o''neal', @output = ''

select @output = @output +
case
when thisChar like '[a-zA-Z]' and lastChar not like '[a-zA-Z]'
then upper(thisChar)
else lower(thisChar)
end
from
(
select
substring(@text,n,1) thisChar,
substring(@text,n-1,1) lastChar
from numbers
) d

select @output

Sharon Matyk
2007-03-12
re: Simple T-SQL Proper Case User-Defined Function
This is helpful, but I need something that recognises initial caps within parentheses and after hyphens. Is there a version of this function that can work with data like JOHN (JAMES) SMITH and JOHN SMITH-JONES?

Sharon Matyk
2007-03-12
re: Simple T-SQL Proper Case User-Defined Function
Sorry, it works on hyphens and parentheses! Cool! thanks for this, it will help enormously in data conversions - SM.

Jeff
2007-03-12
re: Simple T-SQL Proper Case User-Defined Function
Hey Jay -- normally I am always on board with using a numbers table and all that, but in this case I don't think you gain any advantage in terms of performance or readability, and you introduce a dependency on having a numbers table in your database. Still, I agree overall that in general most solutions that can use a numbers table to keep them simple and efficient is the way to go.

Arnold Fribble
2007-03-12
re: Simple T-SQL Proper Case User-Defined Function
Shouldn't it be testing UPPER(c)<>LOWER(c) to decide whether it needs to change anything? Otherwise it will ignore any characters that don't collate between a-z or A-Z in whatever collation it uses.
Tests show that using 'a-z' COLLATE Latin1_General_CI_AI misses 604 out of 1326 characters that have different UPPER and LOWER representations

Jeff
2007-03-12
re: Simple T-SQL Proper Case User-Defined Function
Hi Arnold -- Absolutely, you can and should alter the check any way you want to meet your needs. Maybe I should have ephasized two things better: a) this is just a simple implementation and b) this should be used to assist you only with cleaning up some data that will be eventually stored properly. All kinds of things fail this -- i.e., "It's" will be formatted as "It'S" and so on.

Jay
2007-03-12
re: Simple T-SQL Proper Case User-Defined Function
Jeff, Yep, I kinda figured after I posted that it was intended to be a simple example. Nice to see some familiar "faces" still around here!

Jim
2007-07-02
re: Simple T-SQL Proper Case User-Defined Function
Slight revision using wildcard patterns.. patterns can be added as other exceptions come up, e.g. L'Amore, etc. Note: The argument (@Text,@i-4,5) might have to be adjusted in length depending on the pattern. Example usage below.

CREATE FUNCTION [dbo].[f_ProperCase](@Text as varchar(512)) RETURNS varchar(512) as
BEGIN

DECLARE @Reset bit
DECLARE @Ret varchar(512)
DECLARE @i int
DECLARE @c char(1)

SELECT @Reset = 1, @i=1, @Ret = ''

WHILE @i <= LEN(@Text)
SELECT @c= SUBSTRING(@Text,@i,1),
@Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END,
@Reset= CASE WHEN
CASE WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [DOL]''' THEN 1
WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [D][I]' THEN 1
WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [M][C]' THEN 1
ELSE 0
END = 1
THEN 1
ELSE CASE WHEN @c like '[a-zA-Z]' or @c in ('''') THEN 0
ELSE 1
END
END,
@i = @i +1
RETURN @Ret

-- Test: SELECT dbo.f_ProperCase('it''s crazy! i couldn''t believe kate mcdonald, leo dicaprio, (terrence) trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to [cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test.' )
END

SELECT dbo.f_ProperCase('it''s crazy! i couldn''t believe kate mcdonald, leo dicaprio, (terrence) trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to [cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test.' )

dave
2007-09-11
re: Simple T-SQL Proper Case User-Defined Function
Hi Jim,

I am trying to create a Function (using your example) in MYSQL to change data from UPPER CASE to Proper Case. What are the main differences between t-SQL and MySql.

thanks
Dave

James
2007-11-28
re: Simple T-SQL Proper Case User-Defined Function
Great function. Just be careful of how Name'S gets cased

Etienne
2008-09-08
re: Simple T-SQL Proper Case User-Defined Function
This is MySQL port.

Etienne.-

CREATE FUNCTION `proppercase`(strin varchar(8000)) RETURNS varchar(8000)
NO SQL
DETERMINISTIC
BEGIN

DECLARE
strOut VARCHAR(255);
DECLARE i INT;
DECLARE Up BIT;
DECLARE c VARCHAR(2) ;
IF strIn IS NULL then
RETURN NULL ;
end if ;



SET
strOut = '',
i = 0,
Up = 1 ;

WHILE i <= LENGTH(strIn) do
SET c = SUBSTRING(strIn,i,1) ;
IF c IN (' ','-','''') then
SET strOut = concat(strOut , c) ;
SET Up = 1 ;
ELSE
IF up = 1 then
SET c = UPPER(c) ;
ELSE
SET c = LOWER(c) ;
end if ;
SET strOut = concat(strOut , c) ;
SET Up = 0 ;
END if ;
SET i = i + 1 ;
END WHILE;
RETURN strOut ;
END


Mike
2008-10-10
re: Simple T-SQL Proper Case User-Defined Function
Jim,

Thank you SO much for that!!!!!


Mike

Jack
2009-02-05
re: Simple T-SQL Proper Case User-Defined Function
Thanks -- About to write something with I imagine A LOT of CODE Bloat. This example and the comment posts are what i needed. Thanks!

Rodolfo
2009-03-20
re: Simple T-SQL Proper Case User-Defined Function
Excelent, thank´s from Mexico

Sai
2009-05-26
re: Simple T-SQL Proper Case User-Defined Function
Thanks! Great time saver.

karl
2009-07-09
re: Simple T-SQL Proper Case User-Defined Function
Thanks dude! :D

KB
2009-08-02
re: Simple T-SQL Proper Case User-Defined Function
Great article ! Saved my time !

Michael Liwanag
2009-08-05
re: Simple T-SQL Proper Case User-Defined Function
Hi,

Not sure if this is the right thread, but I just wanted to get some help.
I am trying to put a CREATE FUNCTION statement inside a try block. But wouldn't allow me, is there another way to do this. The purpose of doing the try block is to have some db version control and audit log.

Thanks.

Synpax
2009-10-01
re: Simple T-SQL Proper Case User-Defined Function
There is an error in one Jim is submitting that I can't crack.

I'm running ms sql 2000 and get:

Server: Msg 170, Level 15, State 1, Procedure f_ProperCase, Line 26
Line 26: Incorrect syntax near '@Ret'.

Josh
2010-07-27
re: Simple T-SQL Proper Case User-Defined Function
Exactly what I was looking for, great post!

DMAsuncion
2010-10-06
re: Simple T-SQL Proper Case User-Defined Function
This is great :) Thank you all.