Solved

SQL Update First word to UPPERCASE all other words lowercase

Posted on 2013-01-11
7
603 Views
Last Modified: 2013-01-11
I need a script that will update records and set the first word to UPPERCASE and all remaining words in the string to lowercase.

Example the cltsort field will have either an individuals name or company name.

Reliable Concrete would become RELIABLE concrete
Smith, John would become SMITH, john
Ruby Lake Cafe would become RUBY lake cafe

Any help is greatly appreciated.
0
Comment
Question by:ITMcmcpa
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38767502
This is a starting point:

declare @word char(50)
set @word = 'Smith, John'
select UPPER(substring(@word,1,charindex(' ', @word)-1)) + LOWER(substring(@word, charindex(' ', @word), len(@word)))

Open in new window


However, it requires a space in the string to delineate the first "word".

If you had a string of "Smith,John", your output would be "SMITH,JOHN" because there's no space to pull the first word from.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38767668
If you want to use multiple delimiters for your "first word" you can create a variable, execute and then change the variable.

Something like this:

declare @T table (name varchar(50))
declare @delimiter varchar(1)

insert into @T(name)
select 'Smith, John'
union
select 'Smith John'
union
select 'Smith-John'

set @delimiter = ',' -->> You can change this to your needs

select name as OriginalName, 
	case when charindex(@delimiter, name) >0 
	then UPPER(substring(name,1,charindex(@delimiter, name)-1)) + LOWER(substring(name, charindex(@delimiter, name), len(name)))
	else name end AS FormattedName
from @T

Open in new window


As you can see, if the delimiter exists in the string it will perform the change, if not, it will leave it as it is.

Just expanding a little bit sjwales' idea.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38767714
1) Assumes the the first non-letter (or failing that, the end of the string) denotes the first word break
2) Works even if the entry is all one "word"


CREATE TABLE #tbl (MyColumn varchar(50))

INSERT INTO #tbl (MyColumn) VALUES ('Fred Smith'), ('Smith,Fred'), ('Tommy')

SELECT MyColumn,
    UPPER(LEFT(MyColumn, PATINDEX('%[^a-z]%', MyColumn + ' ') - 1)) + SUBSTRING(MyColumn, PATINDEX('%[^a-z]%', MyColumn + ' '), LEN(MyColumn))
FROM #tbl

DROP TABLE #tbl

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:ITMcmcpa
ID: 38768227
Sorry, I probably was not clear or I don't completely understand the answers.

I would like to actually update the column in the database and not just modify the result.

The column name is Cltname in the table Clients.

Take the existing information in the Cltname column and update the record accordingly.

Existing string in the Cltname field = Ruby Lake Cafe
Update the string in the Cltname field = RUBY lake cafe

Thank you
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38768259
UPDATE MyTable
SET MyColumn = UPPER(LEFT(MyColumn, PATINDEX('%[^a-z]%', MyColumn + ' ') - 1)) + SUBSTRING(MyColumn, PATINDEX('%[^a-z]%', MyColumn + ' '), LEN(MyColumn))
0
 
LVL 13

Accepted Solution

by:
LIONKING earned 500 total points
ID: 38768272
You just need to modify the update that we gave you, something like

UPDATE Clients
SET Cltname = case when charindex(' ', Cltname ) >0 
	then UPPER(substring(Cltname ,1,charindex(' ', Cltname )-1)) + LOWER(substring(Cltname , charindex(' ', Cltname ), len(Cltname)))
	else Cltname end

Open in new window


That's assuming you define the first "space" as the delimiter.
Give it a try and let us know.
0
 

Author Closing Comment

by:ITMcmcpa
ID: 38768582
Worked beautifully.  It updated the first word to UPPERCASE and all remaing words in the string to lowercase.

Thanks again.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 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

21 Experts available now in Live!

Get 1:1 Help Now