PROPERCASING IN SQLSERVER

Does anybody know of a quick TSQL function to propercase text in SQLServer.

This is the sort of things I'm trying at the moment:

UPDATE c_comprof SET c_name = LOWER(c_name)

      UPDATE c_comprof SET c_name = UPPER( SUBSTRING( c_name,1,1 ) ) +
      SUBSTRING( c_name,2,100)
      
      WHILE PATINDEX( '%[ ][a-z]%',c_name )

            UPDATE c_comprof SET c_name =
            SUBSTRING( c_name,1,PATINDEX( '%[ ][a-z]%',c_name ) ) +
            UPPER( SUBSTRING( c_name,PATINDEX( '%[ ][a-z]%',c_name )+1,1 ) ) +
            SUBSTRING( c_name,PATINDEX( '%[ ][a-z]%',c_name )+2,100 )

This tack would work if only the PATINDEX function recognised [a-z] as
lowercase and [A-Z] as upper.
It's driving me NUTS

SPENCER MOULSON
SpencerMAsked:
Who is Participating?
 
bretConnect With a Mentor Commented:
Hi Spencer,

I'm assuming that what you want is every character in the
string lower-case unless it is immediately preceeded by a space.
Unfortunately, I don't have a MS SQL Server to check this on, but
the following does work on a Sybase SQL Server:

drop table c_comprof
go
create table c_comprof (c_name char(60))
go

insert c_comprof values ("Alex karmen JOESEPH RiChArD")
go

UPDATE c_comprof SET c_name = LOWER(c_name)
go
UPDATE c_comprof SET c_name = UPPER( SUBSTRING(c_name,1,1 ) ) + SUBSTRING( c_name,2,100)
go
declare @x int
select @x = PATINDEX( '%[ ][a-z]%',c_name ) from c_comprof
WHILE (@x >0)
  begin
  UPDATE c_comprof SET c_name =
  SUBSTRING( c_name,1,PATINDEX( '%[ ][a-z]%',c_name ) ) +
  UPPER( SUBSTRING( c_name,PATINDEX( '%[ ][a-z]%',c_name)+1,1 ) ) +
  SUBSTRING( c_name,PATINDEX( '%[ ][a-z]%',c_name )+2,100 )
 
  select @x = PATINDEX( '%[ ][a-z]%',c_name ) from c_comprof
  end
go
        c_name            ------------------------------------------------------------

         Alex Karmen Joeseph Richard                                                                                                                                                                                                                                    

(1 row affected)

However, you are indicating that PATINDEX is not distinguishing
between [a-z] and [A-Z] on MS-SQL, so the following revision may
work for you, as it keeps PATINDEX from scanning the section it has already been over by using substring to give a new starting point.  I used PATINDEX with ([a-z,A-Z]) to simulate your problem, the code will probably be more understandable if you change that back to just [a-z].  -bret

drop table c_comprof
go
create table c_comprof (c_name char(55))
go
insert c_comprof values ("Alex karmen JOESEPH RiChArD bret sue DaviD")
go

UPDATE c_comprof SET c_name = LOWER(c_name)
go
UPDATE c_comprof SET c_name = UPPER( SUBSTRING(c_name,1,1 ) ) + SUBSTRING( c_name,2,100)
go
declare @x int
declare @nextword_offset int
select @x = PATINDEX( '%[ ][a-z,A-Z]%',substring(c_name,2,100 )) from c_comprof
WHILE (@x >0)
begin
   
  UPDATE c_comprof SET c_name =
  SUBSTRING( c_name,1,@x+1 ) +
  UPPER( SUBSTRING( c_name,@x+2,1) ) +
  SUBSTRING( c_name,@x+3,100 )
 
  select @nextword_offset = PATINDEX( '%[ ][a-z,A-Z]%',substring(c_name,@x+3,100) ) from c_comprof
  if (@nextword_offset = 0) select @x = 0 else select @x = @x+1 + @nextword_offset
end
 
select "Results", * from  c_comprof
go
          c_name                                                  
 ------- -------------------------------------------------------
 Results Alex Karmen Joeseph Richard Bret Sue David              


Noted problem:  doesn't work right if there is a single space
in front of the first name.

-bret

P.S.  When coding the special cases like "Mc<X>", don't forget
the special case for e.e.cummings!.


0
 
cymbolicCommented:
You know, you really need more logic than this to "properly" proper case strings.  If you are working with names, for instance, McGee will give you a problem woth your logic.  This is really a problem ripe for a small basic (or other language) program that reads the table, modifies the columns in question, and updates them in proper case.  In that instance, there are many libraries (and I can give you some source in basic) that proper case a text string.  Then,  you need to set your edit systems to proper case before they send in, and your server to be case insensitive on compares.


0
 
SpencerMAuthor Commented:
That's fine but I need to do this within the SQLServer Script.
I'm aware that 'Mc's and 'apostrophy  S's cause difficulties but these are secondary issues that I can work with later.
0
 
Pegasus100397Commented:
Spencer,

I understand the first part of your statement but I have a question before I can give you a useful function to solve this problem... are you trying to ProperCase EVERY word in a text string or just the first word? Thanks

Pegasus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.