Solved

PROPERCASING IN SQLSERVER

Posted on 1997-09-19
4
727 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:SpencerM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 9

Expert Comment

by:cymbolic
ID: 1088958
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
 

Author Comment

by:SpencerM
ID: 1088959
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
 
LVL 3

Expert Comment

by:Pegasus100397
ID: 1088960
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
 
LVL 10

Accepted Solution

by:
bret earned 150 total points
ID: 1088961
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question