Mixed Case Values
-
What is the best way to convert an upper/lower case string into a mixed case string in a stored procedure.
E.g.
HAVE A NICE DAY! = Have A Nice Day!
Thanks,
NS.
-
There may be a better way but this is what I thought of
declare @string varchar(500)
,@xmldoc varchar(1000)
,@xmlDocReturn int
,@returnString varchar(200)
-- create xml document
SET @string = 'have a nice day'
SET @string = REPLACE(@string, ' ','" /><item value="')
SET @string = '<item value="' + @string + '" />'
set @xmlDoc = '<STRINGCONV>' + @string + '</STRINGCONV>'
exec sp_xml_preparedocument @xmlDocReturn OUTPUT, @xmlDoc
SELECT @returnString = ''
SELECT @returnString = @returnString + ' ' + CASE WHEN LEN(StringValue) > 1
THEN UPPER(SUBSTRING(StringValue,1,1)) + LOWER(SUBSTRING(StringValue,2,LEN(StringValue)-1))
ELSE
UPPER(StringValue)
END
FROM OPENXML(@xmlDocReturn,'/STRINGCONV/item')
WITH (StringValue VARCHAR(50) '@value')
exec sp_xml_removedocument @xmlDocReturn
select LTRIM(RTRIM(@returnString))
hope this helps
William O'Malley
-
Thanks William. That worked gr8!!!
-
Create a function, then use this function in your stored proc.
/*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*/
CREATE FUNCTION fnTitleCase (@instring nvarchar(256))
RETURNS nvarchar(256)
AS
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
-
Here's a really simple solution to the problem
create procedure StringToMixedCase
(
@str varchar(255) OUTPUT
 
AS
Declare @space smallint
--Add Space to start to aid in the change
SET @space = 0 -- Necessary else the next line returns null
SET @space = charindex(' ',@str,@space )
-- Loop through the string and replace the spaces
WHILE (@space > 0)
Begin
SET @STR = replace(@str,substring(@str,@space,2),upper(substring(@str,@space,2)))
SET @space = charindex(' ',@str,@space+1)
end
SET @STR = rtrim(ltrim(@str))
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply