SQL Quickie - Fix case

I needed to quickly (and dirtily) transform rows which were all upper case into pascal cased, so 'I AM OPIE' become 'I Am Opie'.

Here's something I found and ported to a sql function:

CREATE FUNCTION [dbo].[Casing]
(@instring VARCHAR(255)) 
RETURNS varchar(1000) AS 
BEGIN
   
--usage: SELECT dbo.Casing('I AM A DOG')
    DECLARE @strptr INT
   
DECLARE @strChar char(1)
   
DECLARE @outstring varchar(1000)
   
SET @outstring = ''
   
SET @strptr = 0
   
WHILE @strPtr < len(RTRIM(@instring))
   
BEGIN
   
SET @strptr = @strptr + 1
   
SET @strchar = SUBSTRING(@instring,@strptr,1)
   
IF @strptr = 1
   
SET @outstring = UPPER(@strchar)
   
ELSE
   
IF SUBSTRING(@instring,(@strptr - 1),1) = ' '
   
or SUBSTRING(@instring,(@strptr - 1),1) = ''''
   
SET @outstring = SUBSTRING(@outstring,1,@strptr) +
   
UPPER(@strchar)
   
ELSE
   
SET @outstring = SUBSTRING(@outstring,1,@strptr) +
   
LOWER(@strchar)
   
END
   
RETURN @outstring
END

Posted 03-27-2009 10:07 AM by Michael Nichols
Filed under:

[Advertisement]

Comments

Dan wrote re: SQL Quickie - Fix case
on 03-27-2009 5:16 PM

You might want to use an IN rather than multiple OR statements for the delimiters:

ALTER FUNCTION [dbo].[Casing]

(@instring VARCHAR(255))

RETURNS varchar(1000) AS

BEGIN

   --usage: SELECT dbo.Casing('I AM A DOG')

   DECLARE @strptr INT

   DECLARE @strChar char(1)

   DECLARE @outstring varchar(1000)

   SET @outstring = ''

   SET @strptr = 0

   WHILE @strPtr < len(RTRIM(@instring))

   BEGIN

SET @strptr = @strptr + 1

SET @strchar = SUBSTRING(@instring,@strptr,1)

IF @strptr = 1

SET @outstring = UPPER(@strchar)

ELSE IF SUBSTRING(@instring,(@strptr - 1),1) IN (',', ' ', '''', ']', '[')

SET @outstring = SUBSTRING(@outstring,1,@strptr) + UPPER(@strchar)

ELSE

SET @outstring = SUBSTRING(@outstring,1,@strptr) + LOWER(@strchar)

   END

   RETURN @outstring

END

It will properly format:

PRINT dbo.Casing('this is so cool''yeah,that I think [it] rox!')

Jason Jarrett wrote re: SQL Quickie - Fix case
on 03-29-2009 6:09 PM

We have a similar function, written in a SQL CLR method... It works well and handles things like roman numerals, and some other trickeries...

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)