SQL Server – Loop through an existing table and process a column with a delimited string

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