Jayme Davis' WebLog : Database

Grant me the serenity.... SQL crosstabs

jayme_davis@dell.com — Fri, 05 Sep 2003 15:11:00 GMT

Ever noticed what a pain building crosstabs in SQL can be?... sure, you can buy 3rd party products to do it for you (even some that are only $39), but what if you want to do it yourself? MS Access has the TRANSFORM function that makes life easy.... why not SQL? There are some great articles on various sites with methods to use (such as this), but you'd think SQL would have a built in function... (don't let us down Yukon).

Jayme

ParseNameString

jayme_davis@dell.com — Tue, 17 Jun 2003 18:40:00 GMT

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

jayme_davis@dell.com — Fri, 13 Jun 2003 15:53:00 GMT

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_davis@dell.com — Fri, 06 Jun 2003 04:59:00 GMT

I'm curious if any of the 3 people reading this blog have ever used teradata with .NET... or for that matter, used teradata for warehouse solutions. With Oracle, SQL DCE, Teradata, and the others available today, what *really* performs the best? (stable, fast, etc..)

Jayme