Foliotek Developer Blog

Converting Names to Mixed Case/Camel Case in SQL Server

I was recently given the task to convert names in a database to mixed case. ?Many of them were already in mixed case, so I wanted to leave those alone and just focus on the ones that were in all caps or all lowercase. ?No automated conversion could be perfect, but this got me pretty close. ?To view a post on determining mixed case in Sql Server, click here.

Here’s my strategy:

  • If the name is already in mixed case, trust that it’s right.
  • If the name is not in mixed case, then do the following: - Trim up the name to eliminate white space
  • Account for the names that start with “Mc” like “McDavid”
  • Account for names with apostrophes like O’Reilly
  • Account for hyphenated names (married names) “Anderson-Johnson”
  • Account for multiple word names like “La Russa”
  • Make sure suffixes included in the names field are capitalized appropriately

Here’s the code:

[sourcecode lang="sql"]
CREATE FUNCTION [dbo].[GetCamelCaseName]
(
@Name varchar(50)
)
RETURNS VARCHAR(50) WITH SCHEMABINDING
AS
BEGIN
– Declare the return variable here
DECLARE @NameCamelCase VARCHAR(50)

– This is determining whether or not the name is in camel case already (if the 1st character is uppercase
– and the third is lower (because the 2nd could be an apostrophe). To do this, you have to cast the
– character as varbinary and compare it with the upper case of the character cast as varbinary.

IF (CAST(SUBSTRING(@Name, 1,1) as varbinary) = CAST(SUBSTRING(UPPER(@Name), 1, 1) as varbinary)
AND ((CAST(SUBSTRING(@Name, 2,1) as varbinary) = CAST(SUBSTRING(LOWER(@Name), 2, 1) as varbinary)
AND SUBSTRING(@Name, 2,1) != ””)
or
(CAST(SUBSTRING(@Name, 4,1) as varbinary) = CAST(SUBSTRING(LOWER(@Name), 4, 1) as varbinary)
AND SUBSTRING(@Name, 2,1) = ””)))

BEGIN
SELECT @NameCamelCase = RTRIM(LTRIM(@Name))
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ sr’ as nvarchar(max)),cast( ‘ Sr’ as nvarchar(max)))
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ jr’ as nvarchar(max)),cast( ‘ Jr’ as nvarchar(max)))
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ ii’ as nvarchar(max)),cast( ‘ II’ as nvarchar(max)))
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ iii’ as nvarchar(max)),cast( ‘ III’ as nvarchar(max)))
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ DE ‘ as nvarchar(max)),cast( ‘ de ‘ as nvarchar(max)))
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘macdonald’ as nvarchar(max)),cast( ‘MacDonald’ as nvarchar(max)))

if (@NameCamelCase LIKE ‘% iv’) — avoid changing “Ivan” to “IVan”
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ iv’ as nvarchar(max)),cast( ‘ IV’ as nvarchar(max)))

if ((@NameCamelCase = ‘i’) or (@NameCamelCase = ‘ii’) or (@NameCamelCase = ‘iii’) or (@NameCamelCase = ‘iv’))
SELECT @NameCamelCase = UPPER(@NameCamelCase)

RETURN @NameCamelCase

END

ELSE

BEGIN

SELECT @NameCamelCase = RTRIM(LTRIM(@Name))

– “Mc-”
SELECT @NameCamelCase =
CASE
WHEN @Name LIKE ‘mc%’
THEN UPPER(SUBSTRING(@Name, 1, 1)) + LOWER(SUBSTRING(@Name, 2, 1)) + UPPER(SUBSTRING(@Name, 3, 1)) + LOWER(SUBSTRING(@Name, 4, 47))
ELSE
UPPER(SUBSTRING(@Name, 1, 1)) + LOWER(SUBSTRING(@Name, 2, 49))
END

– Apostrophes
SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE ‘%”%’
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX(””, @NameCamelCase) – 1) + ”” + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(””, @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(””, @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END

– Hyphenated names (do it twice to account for double hyphens)
SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE ‘%-%’
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX(‘-’, @NameCamelCase) – 1) + ‘^’ + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(‘-’, @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(‘-’, @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END

SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE ‘%-%’
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX(‘-’, @NameCamelCase) – 1) + ‘^’ + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(‘-’, @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(‘-’, @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END

SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘^’ as nvarchar(max)),cast( ‘-’ as nvarchar(max)))

– Multiple word names (do it twice to account for three word names)
SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE ‘% %’
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX(‘ ‘, @NameCamelCase) – 1) + ‘?’ + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(‘ ‘, @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(‘ ‘, @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END

SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE ‘% %’
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX(‘ ‘, @NameCamelCase) – 1) + ‘?’ + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(‘ ‘, @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(‘ ‘, @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END

SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘?’ as nvarchar(max)),cast( ‘ ‘ as nvarchar(max)))

– Names in Parentheses
SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE ‘%(%’
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX(‘(‘, @NameCamelCase) – 1) + ‘(‘ + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(‘(‘, @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(‘(‘, @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END

SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ sr’ as nvarchar(max)),cast( ‘ Sr’ as nvarchar(max)))
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ jr’ as nvarchar(max)),cast( ‘ Jr’ as nvarchar(max)))
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ ii’ as nvarchar(max)),cast( ‘ II’ as nvarchar(max)))
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ iii’ as nvarchar(max)),cast( ‘ III’ as nvarchar(max)))
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ DE ‘ as nvarchar(max)),cast( ‘ de ‘ as nvarchar(max)))
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘macdonald’ as nvarchar(max)),cast( ‘MacDonald’ as nvarchar(max)))

if (@NameCamelCase LIKE ‘% iv’)
SELECT @NameCamelCase = REPLACE(cast(@NameCamelCase as nvarchar(max)),cast( ‘ iv’ as nvarchar(max)),cast( ‘ IV’ as nvarchar(max)))

if ((@NameCamelCase = ‘i’) or (@NameCamelCase = ‘ii’) or (@NameCamelCase = ‘iii’) or (@NameCamelCase = ‘iv’))
SELECT @NameCamelCase = UPPER(@NameCamelCase)

– Return the result of the function
RETURN ISNULL(@NameCamelCase, ”)

END

RETURN ISNULL(@NameCamelCase, ”)

END

[/sourcecode]