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