Simple T-SQL Proper Case User-Defined Function

<< Essential SQL Server Date, Time and DateTime Functions | Home | Sometimes the problem isn't the code. It's the specs. >>

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)

Print | posted on Friday, March 09, 2007 8:53 AM | Filed Under [ T-SQL ]

Feedback

# 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

3/10/2007 10:46 PM | Jay (aka ehorn)

# 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?

3/12/2007 7:00 AM | Sharon Matyk

# 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.

3/12/2007 7:28 AM | Sharon Matyk

# 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.

3/12/2007 8:04 AM | Jeff

# 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

3/12/2007 9:45 AM | Arnold Fribble

# 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.

3/12/2007 11:33 AM | Jeff

# 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!

3/12/2007 9:50 PM | Jay

# 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.' )

7/2/2007 10:59 AM | Jim

# 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

9/11/2007 3:25 PM | dave

# re: Simple T-SQL Proper Case User-Defined Function

Great function. Just be careful of how Name'S gets cased

11/28/2007 12:37 PM | James

# 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

9/8/2008 11:16 AM | Etienne

# re: Simple T-SQL Proper Case User-Defined Function

Jim,

Thank you SO much for that!!!!!

Mike

10/10/2008 10:45 AM | Mike

# 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!

2/5/2009 1:23 PM | Jack

# re: Simple T-SQL Proper Case User-Defined Function

Excelent, thank´s from Mexico

3/20/2009 1:53 AM | Rodolfo

# re: Simple T-SQL Proper Case User-Defined Function

Thanks! Great time saver.

5/26/2009 8:25 AM | Sai

# re: Simple T-SQL Proper Case User-Defined Function

Thanks dude! :D

7/9/2009 11:46 PM | karl

# re: Simple T-SQL Proper Case User-Defined Function

Great article ! Saved my time !

8/2/2009 10:18 PM | KB

# 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.

8/5/2009 6:19 PM | Michael Liwanag

# 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'.

10/1/2009 4:18 PM | Synpax

# Error in Jim

Just terminate the function declaration with END just after the @Ret.

3/18/2010 3:09 PM | Bob

# re: Simple T-SQL Proper Case User-Defined Function

Exactly what I was looking for, great post!

7/27/2010 10:37 AM | Josh

# re: Simple T-SQL Proper Case User-Defined Function

This is great :) Thank you all.

10/6/2010 11:54 PM | DMAsuncion

Comments have been closed on this topic.

Jeff's SQL Server Blog

My Links

Advertisement

News

Archives

Post Categories

Programming

Sports

SQL