Solved

PROPERCASING IN SQLSERVER

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query help 3 24
SQL Select Query help 1 36
SQL Convert "vertical" data to "horizontal" data 4 26
MS SQL Server Management Studio R2 4 27
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

740 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