Solved

PROPERCASING IN SQLSERVER

Posted on 1997-09-19
4
696 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
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now