Month: August 2013

As mentioned in my last post (Loop through an existing table), I said that I would expound on its example by reading a column that contains a delimited string. In this case, it will be an address that is delimited by spaces. For example, 500 East Main St. This process will break down the address in parts and insert them into an address table that contains 10 columns, 1 column for each part.

The result will insert a row into the Address that looks like this:

NAGuid= 'A guid'
AddressPart1 = '500'
AddressPart1 = 'East'
AddressPart1 = 'Main'
AddressPart1 = 'St'
AddressPart1 = ''
AddressPart1 = ''
AddressPart1 = ''
AddressPart1 = ''
AddressPart1 = ''
AddressPart1 = ''

Here is the logic:

DECLARE @AddressTable Table (NAGuid uniqueidentifier, AddressPart1 varchar(20), AddressPart2 varchar(20), AddressPart3 varchar(20), AddressPart4 varchar(20), AddressPart5 varchar(20), AddressPart6 varchar(20), AddressPart7 varchar(20), AddressPart8 varchar(20), AddressPart9 varchar(20), AddressPart10 varchar(20))

DECLARE @maxrows INT
DECLARE @rowNum INT
DECLARE @NameAddressGUID uniqueidentifier
DECLARE @LastName varchar(50)
DECLARE @FirstName varchar(50)
DECLARE @MiddleName varchar(50)
DECLARE @TheAddressString varchar(300) = ''

DECLARE @Item VARCHAR(1000)
DECLARE @Position INT
DECLARE @Loop BIT

DECLARE @Address1 char(20) = ''
DECLARE @Address2 char(20) = ''
DECLARE @Address3 char(20) = ''
DECLARE @Address4 char(20) = ''
DECLARE @Address5 char(20) = ''
DECLARE @Address6 char(20) = ''
DECLARE @Address7 char(20) = ''
DECLARE @Address8 char(20) = ''
DECLARE @Address9 char(20) = ''
DECLARE @Address10 char(20) = ''

SELECT @maxRows = COUNT(*)
FROM   NameAddress

SELECT TOP 1 @NameAddressGUID = NameAddress_GUID,
             @LastName = LName,
             @FirstName = FName,
             @MiddleName = MName,
             @TheAddressString    = Address
        FROM NameAddress
    ORDER BY NameAddress_GUID ASC

SET @rowNum = 0

-- Loop until last row is reached
WHILE @rowNum < @maxRows
  BEGIN
      SET @rowNum = @rowNum + 1

       --This is where you can now do something to the record
      IF(right(@TheAddressString, 1) <> ' ' and Len(@TheAddressString) > 0)
		BEGIN
			Set @TheAddressString = @TheAddressString + ' '
		END 

		DECLARE @TheAddressStringCount int = 0
		SET @Loop = CASE WHEN LEN(@TheAddressString) > 0 THEN 1 ELSE 0 END
		WHILE (SELECT @Loop) = 1
			BEGIN
				SELECT @Position = CHARINDEX(' ', @TheAddressString, 1)	
				   IF(@Position > 0)
						BEGIN
							SELECT @Item = SUBSTRING(@TheAddressString, 1, @Position - 1)
							SELECT @TheAddressString = SUBSTRING(@TheAddressString, @Position + 1, LEN(@TheAddressString) - @Position)
							--Put charge into charges variable
							SET @TheAddressStringCount =  @TheAddressStringCount + 1
							IF @TheAddressStringCount = 1 SET @Address1 = @Item
							IF @TheAddressStringCount = 2 SET @Address2 = @Item
							IF @TheAddressStringCount = 3 SET @Address3 = @Item
							IF @TheAddressStringCount = 4 SET @Address4 = @Item
						    IF @TheAddressStringCount = 5 SET @Address5 = @Item
							IF @TheAddressStringCount = 6 SET @Address6 = @Item
							IF @TheAddressStringCount = 7 SET @Address7 = @Item
							IF @TheAddressStringCount = 8 SET @Address8 = @Item
							IF @TheAddressStringCount = 9 SET @Address9 = @Item
							IF @TheAddressStringCount = 10 SET @Address10 = @Item
						END
					ELSE
						BEGIN
							INSERT INTO @AddressTable (NAGuid, AddressPart1, AddressPart2, AddressPart3, AddressPart4, AddressPart5, AddressPart6, AddressPart7, AddressPart8, AddressPart9, AddressPart10)
							   SELECT @NameAddressGUID, @Address1, @Address2, @Address3, @Address4, @Address5, @Address6, @Address7, @Address8, @Address9, @Address10
							SET @Address1 = ''
							SET @Address2 = ''
							SET @Address3 = ''
							SET @Address4 = ''
							SET @Address5 = ''
							SET @Address6 = ''
							SET @Address7 = ''
							SET @Address8 = ''
							SET @Address9 = ''
							SET @Address10 = ''							
							SET @TheAddressString = ''
							SET @Item = ''
							SET @Position = 0
							SET @Loop = 0
						END
			END

       --Read the next row making sure the ID of the next row
       --is greater than previous row
	  SELECT TOP 1 @NameAddressGUID = NameAddress_GUID,
             @LastName = LName,
             @FirstName = FName,
             @MiddleName = MName,
             @TheAddressString    = Address
	    FROM NameAddress
	   WHERE NameAddress_GUID > @NameAddressGUID
    ORDER BY NameAddress_GUID ASC
END

SELECT * FROM @AddressTable

In T-SQL you may want to loop through an existing table and do something. You may want want to use this type of processing in a SSRS report.

Here is an example of walking through a table that simply extracts a person’s first, last, and middle name. I didn’t do anything after the read, but simply added a display that shows the current row in the process. I will post an updated version that shows how to break down a column that is delimited and insert each section into its own column in another table.

Here is the code:

DECLARE @maxrows INT
DECLARE @rowNum INT
DECLARE @NameAddressGUID uniqueidentifier
DECLARE @LastName varchar(50)
DECLARE @FirstName varchar(50)
DECLARE @MiddleName varchar(50)

SELECT @maxRows = COUNT(*)
FROM NameAddress

SELECT @maxrows

SELECT TOP 1 @NameAddressGUID = NameAddress_GUID,
             @LastName = LName,
             @FirstName = FName,
             @MiddleName = MName
FROM NameAddress
ORDER BY NameAddress_GUID ASC

SET @rowNum = 0

-- Loop until last row is reached
WHILE @rowNum < @maxRows
BEGIN
SET @rowNum = @rowNum + 1

--This is where you can now do something to the record
SELECT 'YOU ARE ON ROW ', @rowNum

--Read the next row making sure the ID of the next row
--is greater than previous row
SELECT TOP 1 @NameAddressGUID = NameAddress_GUID,
             @LastName = LName,
             @FirstName = FName,
             @MiddleName = MName
FROM NameAddress
WHERE NameAddress_GUID > @NameAddressGUID
ORDER BY NameAddress_GUID ASC

END