April 22, 2003 at 9:47 am
I have a sring united states of america. I wanted to print it as United States of America.I wanted first characters of all the words capital(U,S,A) other than of(o).Can anyone tell me the query for this.
April 22, 2003 at 10:03 am
Here is a function that should give you what you want. Just to give credit where credit is due, I got this script from: Angela But -- abut@rdns.com.au
CREATE FUNCTION fnTitleCase (@instring nvarchar(256))
RETURNS nvarchar(256)
AS
--This function will evaluate an input string and convert it to title-case format.
-- It uses the delimchars parameter to determine what are the triggering characters
-- to indicate capitalization of the subsequent character
BEGIN
DECLARE @strptr INT
DECLARE @outstring nvarchar(255)
DECLARE @strChar char(1)
DECLARE @delimchars nvarchar(256)
SET @outstring = ''
SET @strptr = 0
IF @delimchars is NULL
/* Plug in typical upper-case delimiters. */
/* NOTE: For localization purposes, you may wish to modify this */
SET @delimchars = ' ''-_.,'
/* Loop through the entire string */
WHILE @strPtr < len(RTRIM(@instring))
BEGIN
SET @strptr = @strptr + 1
SET @strchar = SUBSTRING(@instring,@strptr,1)
IF @strptr = 1 /* Assume that first character must always being
upper-cased*/
SET @outstring = UPPER(@strchar)
ELSE /*Check for other upper-case trigger character */
IF CHARINDEX(SUBSTRING(@instring,(@strptr - 1),1),@delimchars) > 0
-- SET @outstring = SUBSTRING(@outstring,1,@strptr)+UPPER(@strchar)
SET @outstring = @outstring+UPPER(@strchar)
ELSE
SET @outstring = @outstring+LOWER(@strchar)
END
RETURN @outstring
END
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply