asked on

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

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

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

rajvja

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SasDev

ASKER

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'.

chapmandew

and you're using sql 2005?

chapmandew

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')

SasDev

ASKER

oh that might be an issue. I'm using SQL 05 Tools & Utilities within to veiw SQL 2000. Sorry about that

rajvja

Have you gone through the link I have given? Is it giving error too?

SasDev

ASKER

Just checking into that now...

chapmandew

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

SasDev

ASKER

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]

chapmandew

try mine just to see if it works for you as well.

SasDev

ASKER

The solution from chapmandew works as well...Much appreciation to you both! Thank you for showing me how to do this in the future.

SasDev

ASKER

Thanks Again!

Traci M.