[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PROPERCASING IN SQLSERVER

Posted on 1997-09-19
4
Medium Priority
?
761 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 450 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

873 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