November 15, 2009 at 10:41 pm
Hi everybody,:-)
Can anybody tell me that what function do we use to convert upper or lower case words into mixed case (Initial caps). example: UPPER, lower should be displayed as Upper, Lower.
Thanks in advance.
November 16, 2009 at 12:36 am
This is called 'Title' case I guess. I dont think we have any built in functions in ss2k5 ( not sure about ss2k8). You might need to write an UDF for that!
---------------------------------------------------------------------------------
November 16, 2009 at 1:15 am
THIS FUNCTION MAY BE HELP YOU
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:VBPROGRAMMER1986@GMAIL.COM
-- Description:THIS FUNCTION IS IMPLEMENTAION OF ORACLES'S INITCAP FUNCTION
-- =============================================
CREATE FUNCTION [dbo].[INITCAP] (@sInitCaps varchar(2000)) RETURNS varchar(2000) AS
BEGIN
DECLARE @nNumSpaces int,
@sParseString varchar(8000),
@sParsedString varchar(8000)
SELECT @nNumSpaces = (LEN(@sInitCaps) - LEN(REPLACE(@sInitCaps, ' ', '')))+1
SELECT @sInitCaps = REPLACE(@sInitCaps, ' ', '%')
WHILE @nNumSpaces > 0
BEGIN
IF (CHARINDEX('%', @sInitCaps) <> 0)
BEGIN
SELECT @sParseString = SUBSTRING(@sInitCaps, 1, CHARINDEX('%',@sInitCaps))
SELECT @sInitCaps = SUBSTRING(@sInitCaps, (CHARINDEX('%', @sInitCaps)+1), LEN(@sInitCaps))
SELECT @nNumSpaces = @nNumSpaces - 1
END
ELSE
BEGIN
SELECT @nNumSpaces = @nNumSpaces - 1
SELECT @sParseString = @sInitCaps
END
SELECT @sParsedString = REPLACE(ISNULL(@sParsedString, '') + CASE WHEN LEN(@sParseString) = 1 THEN UPPER(@sParseString)
ELSE UPPER(SUBSTRING(@sParseString, 1, 1)) + LOWER(SUBSTRING(@sParseString, 2, LEN(@sParseString)))
END, '%', ' ')
END
RETURN(@sParsedString)
END
November 16, 2009 at 3:02 am
-- =============================================
-- Author: VBPROGRAMMER1986@GMAIL.COM
-- Description: THIS FUNCTION IS IMPLEMENTAION OF ORACLES'S INITCAP FUNCTION
-- =============================================
CREATE FUNCTION [dbo].[INITCAP] (@sInitCaps varchar(2000)) RETURNS varchar(2000) AS
BEGIN
DECLARE @nNumSpaces int,
@sParseString varchar(8000),
@sParsedString varchar(8000)
:Wow:Thanks for the reply. This works good. Thanks for your help.:Wow:
November 17, 2009 at 9:19 am
:w00t: Agghhh a while loop. I'll be back with an alternative solution, unless someone beats me to it (highly likely).
In the meantime read Jeffs tally table article for a clue. (see link below)
November 17, 2009 at 4:49 pm
Here is another method that works for basic needs where quoted words and special strings like those found in names are not involved. (i.e. Jim O'Brian, III DDS).
declare @string varchar(250)
select @string = 'NOW is The TIme FoR All Quick Brown Dogs TO JUMp OvEr THE lAzY Fox.'
select substring(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(lower(' ' + @string),
' a', ' A'),
' b', ' B'),
' c', ' C'),
' d', ' D'),
' e', ' E'),
' f', ' F'),
' g', ' G'),
' h', ' H'),
' i', ' I'),
' j', ' J'),
' k', ' K'),
' l', ' L'),
' m', ' M'),
' n', ' N'),
' o', ' O'),
' p', ' P'),
' q', ' Q'),
' r', ' R'),
' s', ' S'),
' t', ' T'),
' u', ' U'),
' v', ' V'),
' w', ' W'),
' x', ' X'),
' y', ' Y'),
' z', ' Z')
,2,249)
November 18, 2009 at 2:25 am
Found someone who has already done something similar to what I was attempting to do.
See here
Hope this helps.
November 18, 2009 at 7:04 am
The function we're using in-house is from Jeff Moden, whose Tally table article Nigel already pointed you to. Uses that to achieve Proper Case (aka Title Case)
http://www.sqlservercentral.com/Forums/Topic530630-8-2.aspx
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 18, 2009 at 7:12 am
jcrawf02 (11/18/2009)
The function we're using in-house is from Jeff Moden, whose Tally table article Nigel already pointed you to. Uses that to achieve Proper Case (aka Title Case)
Thanks, not seen that one before - now bookmarked
November 19, 2009 at 3:30 am
If you need fast and accurate results (which is also languages and culture sensitive!) consider adding a method based on TextInfo.ToTitleCase to your CLR utility library.
See http://msdn.microsoft.com/en-us/library/system.globalization.textinfo.totitlecase(VS.80).aspx for examples and documentation. One line of code is all that is generally required 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 19, 2009 at 6:50 am
Paul White (11/19/2009)
[...](which is also languages and culture sensitive!) [...]
Awfully touchy-feelly stuff coming from a guy who exterminated the protectors of the galaxy in a fit of power-hungry revenge.
Thanks for the link... 😉
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 19, 2009 at 7:00 am
jcrawf02 (11/19/2009)
Paul White (11/19/2009)
[...](which is also languages and culture sensitive!) [...]Awfully touchy-feelly stuff coming from a guy who exterminated the protectors of the galaxy in a fit of power-hungry revenge.
Hey Jon,
I know! I'm clearly not well at the moment. :laugh:
Funny!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 23, 2009 at 6:06 pm
Here is a Function that works nicely.... Enjoy...
CREATE FUNCTION PROPERCASE
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
IF @input IS NULL
BEGIN
--Just return NULL if input string is NULL
RETURN NULL
END
--Character variable declarations
DECLARE @output varchar(8000)
--Integer variable declarations
DECLARE @ctr int, @len int, @found_at int
--Constant declarations
DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int
--Variable/Constant initializations
SET @ctr = 1
SET @len = LEN(@input)
SET @output = ''
SET @LOWER_CASE_a = 97
SET @LOWER_CASE_z = 122
SET @Delimiter = ' ,-'
SET @UPPER_CASE_A = 65
SET @UPPER_CASE_Z = 90
WHILE @ctr <= @len
BEGIN
--This loop will take care of reccuring white spaces
WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
SET @ctr = @ctr + 1
END
IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
BEGIN
--Converting the first character to upper case
SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END
SET @ctr = @ctr + 1
WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
BEGIN
IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
BEGIN
SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END
SET @ctr = @ctr + 1
END
END
RETURN @output
END
November 23, 2009 at 6:28 pm
My eyes!!!!!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 23, 2009 at 6:36 pm
Sorry Paul! How do I put it in a code window?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply