Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

SQL Update First word to UPPERCASE all other words lowercase

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
ITMcmcpa
Asked:
ITMcmcpa
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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
 
LIONKINGCommented:
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
 
Patrick MatthewsCommented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
ITMcmcpaAuthor Commented:
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
 
Patrick MatthewsCommented:
UPDATE MyTable
SET MyColumn = UPPER(LEFT(MyColumn, PATINDEX('%[^a-z]%', MyColumn + ' ') - 1)) + SUBSTRING(MyColumn, PATINDEX('%[^a-z]%', MyColumn + ' '), LEN(MyColumn))
0
 
LIONKINGCommented:
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
 
ITMcmcpaAuthor Commented:
Worked beautifully.  It updated the first word to UPPERCASE and all remaing words in the string to lowercase.

Thanks again.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now