Jayme Davis' WebLog

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

SQL Compare tools

Dealing with 10+ databases, terabytes of data, and enormous DTS packages on a daily basis, I couldn't live without a SQL database comparison tool. I was reading an article about Red Gate software's SQL tools suite which includes SQL Compare (database structures/schemas), SQL Data Compare (content/data), and DTS Compare (dts packages, logins, jobs, and version info). I've used AdeptSQL for a long time now, but it just doesn't cut it for everything...

Jayme

Teradata

Jayme