URGENT Proper Case (AKA Title or Sentence Case) Function
-
We have UPPER() and LOWER() but SQL Svr 2k does not have a function to put strings into their proper case. i.e. Bob vs bob or BOB, O'Mally vs o'mally or O'MALLY or O'mally, etc. Does anyone have a good (read fast) function to do this?
URGENT
Kurt Allebach -
Well, it doesn't treat o'malley as O'Malley, but it's close..
(you have to modify yourself for that)
Anyway, here's a combined properCase and whitespace trimmer UDF I wrote. Use it if you like it.
-- Script downloaded 5/6/2004 5:06:34 AM
-- From The SQL Server Worldwide User's Group (www.sswug.org)
-- Scripts provided AS-IS without warranty of any kind use at your own risk
if object_id('dbo.properCase') is not null drop function dbo.properCase
go
create function dbo.properCase ( @string varchar(8000) )
returns varchar(8000)
as
-- T-SQL combined whitespace trimmer and 'proper case' function - SQL Server 2000
-- 2002-10-23 © Kenneth Wilhelmsson
-- Example: Select dbo.properCase(notes) from pubs..titles to see all notes proper cased.
-- Example2: select dbo.properCase(' a somewhat irregular string ')
begin
-- return null if input is null
if ( @string is null ) return null
declare @test varchar(8000), @propcase varchar(8000)
-- init
select @propcase = ''
-- remove embedded multiple spaces between words
-- we do this by replacing 2 spaces with 1 as long as there are pairs...
while patindex('% %', @string) > 0
begin
select @string = replace(@string,' ',' ')
end
-- if we still have leading spaces..
while substring(@string,1,1) = ' '
begin
set @string = substring(@string,2,8000)
end
-- or trailing spaces...
set @string = reverse(@string)
while substring(@string,1,1) = ' '
begin
set @string = substring(@string,2,8000)
end
set @string = reverse(@string)
-- done, if there still are spaces inside, begin...
while patindex('% %',@string) > 0
begin
-- grab the first 'word'
set @test = substring(@string,1,charindex(' ',@string))
-- then propCase it
set @test = upper(substring(@test,1,1)) +
lower(substring(@test,2,len(@test)-1)) + ' '
-- save away the cased word
set @propcase = @propcase + @test
-- trim the string from the used 'word'
set @string = substring(@string,(len(@test)+2),8000)
-- if there are more spaces - do it all over again with the next 'word'....
-- except, if the 2nd next char is a space - then trim away one
while substring(@string,1,1) = ' ' and substring(@string,2,1) = ' '
begin
set @string = substring(@string,2,8000)
end
end
-- if no spaces, it's a single word, or the last word from the loop....
if patindex('% %',@string) = 0
begin
-- then just propCase the single word
set @test = upper(substring(@string,1,1)) +
lower(substring(@string,2,len(@string)-1))
-- save away the cased word
set @propcase = @propcase + @test
end
-- done
return @propcase
end
go
-
CREATE FUNCTION xProper(@StringVal AS Varchar(8000)) RETURNS Varchar(8000)
BEGIN
Declare @Char As Varchar(1), @Len As Int, @NewVal As Varchar(8000), @Pos As Int, @PrevChar AS Varchar(1)
If @StringVal Is Null Return Null
Select @Char='', @NewVal='', @Pos = 1, @Len=DATALENGTH(@StringVal), @StringVal=Upper(@StringVal)
WHILE @Pos <= @Len BEGIN
Set @Char=SUBSTRING(@StringVal, @Pos, 1)
Set @PrevChar=SUBSTRING(@StringVal, @Pos-1, 1)
If ASCII(@PrevChar) Between 65 And 90 OR ASCII(@PrevChar) Between 97 And 122 Set @Char=Lower(@Char)
Set @NewVal=@NewVal + @Char
SET @Pos = @Pos + 1
END
RETURN @NewVal
END
-
A nice and short UDF.
It does have some limitations, though.
While it cases o'malley as O'Malley successfully, it also cases words like don't and can't as Don'T and Can'T.
For many of you a-z speaking guys the method of using ascii ranges is good enough, however for us with extended ascii in our languages, this won't work. The lack of extended ascii support makes casing rather eLiTe looking.
eg 'jag äter rökt ål' becomes 'Jag ÄTer RöKt ÅL'
..for the curious this means 'I Eat Smoked Eel'
Anyway, bottom line is that I don't think there is any perfect 'propcaser' anywhere. It's always difficult to handle the exceptions. (like MacDuff or McDuff etc..)
/Kenneth
-
Thx to all. To date I have recieved 6 scripts and we are testing each for function and performance. We may use one as is or combine with elements from many. In any case I will be posting my results shortly. Stay tuned.
Kurt Allebach -
Kurt,
You may also want to try this:
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.<BR>I am using it in a DTS package to convert data in CAPS to Title/Proper case as I output the data to an Excel file.
/*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
Cheers,
Angela
-
Here is my contribution to this old question:
CREATE FUNCTION dbo.udf_ProperCase
(@StringVal AS Varchar(8000))
RETURNS Varchar(8000)
AS
/*******************************************************************************************
<cdCmnt>
<descr>Returns a string as proper case using ASCII values
After scouring the internet, found many, many proper case functions
but this was the only one that handled all of the following:
B.C., N.W.T., A.J.M., G/L, R.C.M.P., C E, Brenda J., Jo-Anne, Donald Brian, Nikka L. A., Allan (Harland)
The contribution was from:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=114551#bm114674
Contributor RobertPaquette
...HOWEVER, it DID NOT handle JAY'S or addresses like 8th. So, I customized it to handle this
Examples:
SELECT dbo.udf_ProperCase('hi')
SELECT dbo.udf_ProperCase('')
SELECT dbo.udf_ProperCase('O''Reilly')
SELECT dbo.udf_ProperCase('Jay''s')
SELECT dbo.udf_ProperCase('JAY''S')
SELECT dbo.udf_ProperCase('Jay''s Moving')
SELECT dbo.udf_ProperCase('R.C.M.P')
SELECT dbo.udf_ProperCase('R.C.M.P.')
SELECT dbo.udf_ProperCase('G/L')
SELECT dbo.udf_ProperCase('8TH')
</descr>
<parm nm='@StringVal'>
<descr>Input string</descr>
</parm>
<rvsn>
<auth>dtomyn</auth>
<dt>2004/05/12</dt>
<descr>Created</descr>
</rvsn>
</cdCmnt>
*******************************************************************************************/
BEGIN
DECLARE
@Char AS VARCHAR(1)
, @Len AS INT
, @NewVal AS VARCHAR(8000)
, @Pos AS INT
, @PrevChar AS VARCHAR(1)
, @NextChar AS VARCHAR(1) --added
IF @StringVal IS NULL
RETURN NULL
SELECT
@Char=''
, @NewVal=''
, @Pos = 1
, @Len=DATALENGTH(@StringVal)
, @StringVal=Upper(@StringVal)
WHILE @Pos <= @Len
BEGIN
SET @Char=SUBSTRING(@StringVal, @Pos, 1)
SET @PrevChar=SUBSTRING(@StringVal, @Pos-1, 1)
SET @NextChar=SUBSTRING(@StringVal, @Pos+1, 1) --added
IF ASCII(@PrevChar) = 39 --if ' added
BEGIN --added
--if at end of string then datalength will be 0 and this character should NOT be capitalized
IF DATALENGTH(@NextChar) = 0 --added
SET @Char=LOWER(@Char) --added
ELSE
BEGIN
--if next character is a space than the letter should NOT be capitalized
IF ASCII(@NextChar) = 32 --added
SET @Char=LOWER(@Char) --added
ELSE
SET @Char=UPPER(@Char) --added
END
END --added
ELSE
BEGIN
IF ASCII(@PrevChar) Between 65 And 90 OR ASCII(@PrevChar) Between 97 And 122
SET @Char=LOWER(@Char)
ELSE --added
BEGIN --added
--if previous character was a number then change the case to a lower
IF ASCII(@PrevChar) Between 48 And 57 --added
SET @Char = LOWER(@Char) --added
ELSE --added
SET @Char=UPPER(@Char) --added
END
END
SET @NewVal=@NewVal + @Char
SET @Pos = @Pos + 1
END
RETURN RTRIM(LTRIM(@NewVal))
-- RETURN @NewVal
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply