Solved

SQL Update First word to UPPERCASE all other words lowercase

Posted on 2013-01-11
7
602 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 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