INITCAPS equivilent in SQL Server

[petereit]

I'm using Microsoft SQL Server v7.0 and am trying to convert a text string to Upper/Lower case so that the first letter in each word in the string is capitalized (like Oracle's INITCAPS function). For Example, the string "the quick brown fox" would be converted to "The Quick Brown Fox".

Anyone have any idea how this can be done in a SQL statement on Microsoft SQL Server?

 

[karluk]

You may have already checked, but BOL has a list of Oracle functions and their SQL Server counterparts. It says that there is no equivalent for INITCAPS. If you really need this, it looks as if you will have to write your own function to search a text string for spaces and other separators, and replace initial letters with caps.

 

[petereit]

Yes, I have already determined that. That's why I'm asking the question here. I hope someone has already solved this problem and can provide me with code.

 

[karluk]

I did some testing in Oracle and found that initcap converts lower case to upper case for the first character of a string, or if the preceding character of the string has any of the following ASCII values:

0-47, 58-64, 91-96, 123-191, 215, 247

Perhaps someone can convert this info into a valid SQL Server function.

 

[tlbroadbent]

Check the SQL Server FAQ -

http://www.mssqlserver.com/faq/.

It is a great resource.

Neil Pike created a proper case stored procedure and the T-SQL code is listed in the FAQ at the following link.

http://www.mssqlserver.com/faq/development-proper.asp

After copying the code and creating your SP, test it with the following T-SQL code.

Declare @name varchar(255)
Set @name='SANDRA g. richardson'
Exec sp_proper @name output
Print @name

The result should be Sandra G. Richardson. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin