?
Solved

Use Uppercase within spaced last names such as 'Mc Donnald'

Posted on 2010-01-07
13
Medium Priority
?
257 Views
Last Modified: 2012-05-08
Question: I have been having a hard time trying to figure out how to capitalize the second portion of a last name that has a space in it such as the capitalization of the last name 'Mc donnald'. I would like the d within 'Donnald' capitalized as such. Here is a snippet of my code and a few lines within the result set:

SELECT     TOP 100 PERCENT CASE WHEN dbo.tblemployees.nickname <> '' THEN CONVERT(varchar(50), upper(substring(dbo.tblemployees.nickname, 1, 1))
                      + lower(substring(dbo.tblemployees.nickname, 2, 49))) ELSE CONVERT(varchar(50), upper(substring(dbo.tblemployees.fname, 1, 1))
                      + lower(substring(dbo.tblemployees.fname, 2, 49))) END AS [First Name], CONVERT(varchar(50), UPPER(SUBSTRING(dbo.tblEmployees.lname, 1, 1))
                      + LOWER(SUBSTRING(dbo.tblEmployees.lname, 2, 49))) AS [Last Name], dbo.lkpDepartments.Department, dbo.tblEmployees.ext AS [Ext.],
                      dbo.vw_ADSI.mail AS [Work Email]

Joan      Mc call        Warehouse            NULL
Sally      Mc donald        Warehouse            NULL
Ryan      Mc elheny        Warehouse            NULL
Janelle      Mc garry        Information Technology      315      Janelle.McGarry@sasinc.com

Any help would be greatly appreciated!

-- Traci
0
Comment
Question by:SasDev
  • 6
  • 5
  • 2
13 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 26200081
0
 
LVL 11

Assisted Solution

by:rajvja
rajvja earned 1000 total points
ID: 26200122
0
 
LVL 1

Author Comment

by:SasDev
ID: 26200172
Thank you for the assistance. However, I am receiveing these errors while executing this script.

Msg 170, Level 15, State 1, Procedure udf_CorrectCasing, Line 8
Line 8: Incorrect syntax near 'MAX'.
Msg 170, Level 15, State 1, Procedure udf_CorrectCasing, Line 12
Line 12: Incorrect syntax near 'MAX'.
Msg 137, Level 15, State 2, Procedure udf_CorrectCasing, Line 15
Must declare the variable '@string'.
Msg 137, Level 15, State 1, Procedure udf_CorrectCasing, Line 18
Must declare the variable '@NewString'.
Msg 137, Level 15, State 2, Procedure udf_CorrectCasing, Line 19
Must declare the variable '@String'.
Msg 137, Level 15, State 2, Procedure udf_CorrectCasing, Line 23
Must declare the variable '@String'.
Msg 137, Level 15, State 2, Procedure udf_CorrectCasing, Line 24
Must declare the variable '@NewString'.
Msg 137, Level 15, State 2, Procedure udf_CorrectCasing, Line 29
Must declare the variable '@NewString'.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 60

Expert Comment

by:chapmandew
ID: 26200195
and you're using sql 2005?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26200204
this works fine for me:

CREATE FUNCTION udf_CorrectCasing
 (
     @String VARCHAR(MAX)
 )
 RETURNS VARCHAR(MAX)
 BEGIN
     DECLARE @Length INT, @Increment INT, @NewString VARCHAR(MAX)
     DECLARE @CurrentCharacter CHAR(1), @PreviousCharacter CHAR(1)    

     SET @Length = LEN(LTRIM(RTRIM(@string)))
     SET @Increment = @Length - 1
     SET @NewString = ''
     SET @PreviousCharacter = ''
     SET @String = LOWER(@String)    

     WHILE @Increment >= 0
     BEGIN
         SET @CurrentCharacter = SUBSTRING(@String, (@Length-@Increment), 1)      
         SET @NewString = @NewString + CASE WHEN @PreviousCharacter = '' THEN  
                  UPPER(@CurrentCharacter) ELSE @CurrentCharacter END
         SET @PreviousCharacter = @CurrentCharacter
         SET @Increment = @Increment - 1
     END
     RETURN(@NewString)
   
 END
  GO
 
  select dbo.udf_CorrectCasing('tim chapman')
0
 
LVL 1

Author Comment

by:SasDev
ID: 26200210
oh that might be an issue. I'm using SQL 05 Tools & Utilities within to veiw SQL 2000. Sorry about that
0
 
LVL 11

Expert Comment

by:rajvja
ID: 26200220
Have you gone through the link I have given? Is it giving error too?
0
 
LVL 1

Author Comment

by:SasDev
ID: 26200225
Just checking into that now...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26200465
this will work for 2000:

CREATE FUNCTION udf_CorrectCasing
 (
     @String VARCHAR(4000)
 )
 RETURNS VARCHAR(4000)
 BEGIN
     DECLARE @Length INT, @Increment INT, @NewString VARCHAR(4000)
     DECLARE @CurrentCharacter CHAR(1), @PreviousCharacter CHAR(1)    

     SET @Length = LEN(LTRIM(RTRIM(@string)))
     SET @Increment = @Length - 1
     SET @NewString = ''
     SET @PreviousCharacter = ''
     SET @String = LOWER(@String)    

     WHILE @Increment >= 0
     BEGIN
         SET @CurrentCharacter = SUBSTRING(@String, (@Length-@Increment), 1)      
         SET @NewString = @NewString + CASE WHEN @PreviousCharacter = '' THEN  
                  UPPER(@CurrentCharacter) ELSE @CurrentCharacter END
         SET @PreviousCharacter = @CurrentCharacter
         SET @Increment = @Increment - 1
     END
     RETURN(@NewString)
   
 END
  GO
0
 
LVL 1

Author Comment

by:SasDev
ID: 26200515
After implementing the function given by rejvja and the calling the function within my selcetion I received the correct result set.
SELECT     TOP 100 PERCENT CASE WHEN dbo.tblemployees.nickname <> '' THEN CONVERT(varchar(50), upper(substring(dbo.tblemployees.nickname, 1, 1))
                      + lower(substring(dbo.tblemployees.nickname, 2, 49))) ELSE CONVERT(varchar(50), upper(substring(dbo.tblemployees.fname, 1, 1))
                      + lower(substring(dbo.tblemployees.fname, 2, 49))) END AS [First Name], dbo.InitCap(dbo.tblEmployees.lname) AS [Last Name],
                      dbo.lkpDepartments.Department, dbo.tblEmployees.ext AS [Ext.], dbo.vw_ADSI.mail AS [Work Email]
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26200532
try mine just to see if it works for you as well.
0
 
LVL 1

Author Comment

by:SasDev
ID: 26200534
The solution from chapmandew works as well...Much appreciation to you both! Thank you for showing me how to do this in the future.
0
 
LVL 1

Author Closing Comment

by:SasDev
ID: 31674006
Thanks Again!

Traci M.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

864 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