RE: Proper case function

[Raster, Tim]

Here is a crappy one...


CREATE FUNCTION Proper (@S varchar(8000)) returns varchar(8000) as


BEGIN


declare @MAC_EXCEPTIONS varchar(255)  set @MAC_EXCEPTIONS =
',Macon,Maceo,Mackinac,Mackinac Island,Mackinaw,Mackinaw City,Macks
Creek,Macks Inn,Macom,Macomb,Macombtownship,Macon,Macy,'
declare @LENS int
declare @B int, @MyLoop int, @CharToFind varchar(1)


If @S > ''
            begin
                        set @LENS = datalength(@S)
                        set @S = lower (@S)
                        set @MyLoop = 0
                        while @MyLoop < 4
                                    begin
                                                set @MyLoop = @MyLoop +
1
                                                set @CharToFind = case
@MyLoop
                                                            when 1 then
' '
                                                            when 2 then
'-'
                                                            when 3 then
'/'
                                                            when 4 then
''''
                                                            end
                                                set @S = upper(Left(@S,
1)) + Right(@S, @LENS - 1)
                                                set @B =
charindex(@CharToFind, @S, 1)
                                                While @B > 0 begin
                                                            if @B <
@LENS


set @S = Left(@S, @B) + upper(substring(@S, @B + 1, 1)) + Right(@S,
@LENS - @B - 1)
                                                            set @B =
charindex(@CharToFind, @S, @B+1)
                                                end
                                    end
                        if Left(@S, 2) = 'Mc' and charindex(@S,
@MAC_EXCEPTIONS) = 0 and @LENS > 2 set @S = 'Mc' + upper(substring(@S,
3, 1)) + case @LENS when 3 then '' else right(@S, @LENS - 3) end
                        if Left(@S, 3) = 'Mac' And charindex(@S,
@MAC_EXCEPTIONS) = 0 And @LENS > 3 set @S = 'Mac' + upper(substring(@S,
4, 1)) + case @LENS when 4 then '' else right(@S, @LENS - 4) end
            end


return @S


END

-----Original Message-----
From: stas [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 03, 2004 10:14
To: SQL
Subject: Re: Proper case function


Thanks, Nancy, but I was hoping for someething on the SQL side...
  ----- Original Message -----
  From: Graham Nancy (air1nxg)
  To: SQL
  Sent: Tuesday, February 03, 2004 11:10 AM
  Subject: RE: Proper case function

  I use the following script but it has a problem with names like
McIntire. It
  does not account for a second cap. If anyone has a fix for this let me
know.
  <cfscript>

  function CapFirst(str) {
  var newstr = "";
  var word = "";
  var i = 1;
  var strlen = listlen(str," ");
  for(i=1;i lte strlen;i=i+1) {
  word = ListGetAt(str,i," ");
  newstr = newstr & UCase(Left(word,1));
  if(len(word) gt 1) newstr = newstr &
  LCase(Right(word,Len(word)-1));
  if(i lt strlen) newstr = newstr & " ";
  }
  return newstr;
  }
  </cfscript>

  Nancy

  -----Original Message-----
  From: stas [mailto:[EMAIL PROTECTED]
  Sent: Tuesday, February 03, 2004 11:07 AM
  To: SQL
  Subject: Proper case function

  Is there a function that will convert an all caps name to proper case?

  Thanks
  _____

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

---