Solved

SQL Update First word to UPPERCASE all other words lowercase

Posted on 2013-01-11
7
605 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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