mmsql - transact

How change every first character string as Upper Case transact sql ?

example
-------
tom and jerry
jerry and tom

result upper
=========
Tom And Jerry
Jerry And Tom

Re: mmsql - transact

info (informatyk@fitness[CUT]authority.pl) writes:
> How change every first character string as Upper Case transact sql ?
>
> example
> -------
> tom and jerry
> jerry and tom
>
> result upper
>=========
> Tom And Jerry
> Jerry And Tom

If you are on SQL 2005, the absolutely best option is to write a
function in a CLR language for the task. That will be a lot more
efficient.

In T-SQL, you would have to loop over the string, looking at the present
and the previous charcater, and apply upper if the current character
is non-sepace, and the previous is space. Something like:

SELECT @prev_ch = ' ', @ix = 1, @newstr = ''
WHILE @ix <= len(@str)
BEGIN
SELECT @ch = substring(@str, @ix, 1)
IF @prev_ch = ' ' AND @ch <> ' '
SELECT @ch = upper(@ch)
SELECT @newstr = @newstr + @ch
SELECT @prev_ch = @ch, @ix = @ix + 1
END
SELECT @str = @newstr

But this very mechanical changing is not going be very nice. Title-
case is a lot more complicated than this. For instance, the correct
title case of "tom and jerry" is "Tom and Jerry". Small words like
conjunctions and prepositions are not to be capitalised. In English
that is. But is also about the only language that I know that uses
title-case. It does not seem to me that Polish uses it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx