Solved

SQL Update First word to UPPERCASE all other words lowercase

Posted on 2013-01-11
7
609 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
[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
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 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