ParseNameString
Well, I'm pretty sure someone is going to tell me I didn't have to do this complicated way, but.. too late! :) I looked around the internet for a SQL parsing function that would parse to proper capitilization, but no luck. I'm lazy, so I decided too write it...
All the name fields in a SQL database I'm working with are all ucase. I wrote this SQL function to parse out proper capitilization for spaces, hyphens, apostrophes, etc... For instance, "MCDONALDS" would parse to "McDonalds"... the only problem is names like "de la Jose" or "von Mirkin" would parse out as "De La Jose" and "Von Mirkin". Even though this problem exist, it's a lot better than it was.
CREATE FUNCTION [dbo].[ParseNameString] (@text VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
DECLARE @n INT
SET @n = 1
DECLARE @newstring VARCHAR(50)
SET @newstring = ''
SET @text = UPPER(LEFT(@text, 1)) + LOWER(RIGHT(@text, LEN(@text) - 1))
-- start string building loop
WHILE (@n < LEN(@text) + 1)
BEGIN
IF(SUBSTRING(@text, @n, 1) = 'M')
BEGIN
-- McXXXXX
IF(SUBSTRING(@text, @n + 1, 1) = 'c')
BEGIN
SET @newstring = @newstring + 'Mc' + UPPER(SUBSTRING(@text, @n + 2, 1))
SET @n = @n + 3
END
-- name MacXXXXX
IF(SUBSTRING(@text, @n + 1, 1) = 'a')
BEGIN
IF(SUBSTRING(@text, @n + 2, 1) = 'c')
BEGIN
SET @newstring = @newstring + 'Mac' + UPPER(SUBSTRING(@text, @n + 3, 1))
SET @n = @n + 4
END
END
END
-- apost. in the name (exmple: O'Sullivan)
IF (SUBSTRING(@text, @n, 1) = '''')
BEGIN
SET @newstring = @newstring + SUBSTRING(@text, @n, 1) + UPPER(SUBSTRING(@text, @n + 1, 1))
SET @n = @n + 1
END
ELSE
-- space in the last name
IF(SUBSTRING(@text, @n, 1) = ' ')
BEGIN
SET @newstring = @newstring + SUBSTRING(@text, @n, 1) + UPPER(SUBSTRING(@text, @n + 1, 1))
SET @n = @n + 1
END
ELSE
-- hyphen in the last name
IF(SUBSTRING(@text, @n, 1) = '-')
BEGIN
SET @newstring = @newstring + SUBSTRING(@text, @n, 1) + UPPER(SUBSTRING(@text, @n + 1, 1))
SET @n = @n + 1
END
ELSE
BEGIN
SET @newstring = @newstring + SUBSTRING(@text, @n, 1)
END
SET @n = @n + 1
END
-- return the newly built string
RETURN @newstring
END