SQL Cursor for Parsing Address Data

Cursor for taking address data in one field in the following formats:

"Seattle, WA 98122"

or

"Seattle, WA 98122-0087"

and splitting it into 3 columns:

"Seattle", "WA", "98122-0087"

Needs a UDF called: ExtractInteger to extract number data from the field. This is available online or via Kevin.

DECLARE XferCursor CURSOR

FOR

SELECT    [Address 2], [Address 3], [Address 4], [Address 5]

FROM    QB

WHERE [Address 2] IS NOT NULL

FOR UPDATE

DECLARE @Add2 varchar(max)

DECLARE @Add3 varchar(max)

DECLARE @Add4 varchar(max)

DECLARE @Add5 varchar(max)

DECLARE @commalocation int

DECLARE @add3size int

DECLARE @zipnums varchar(max)

DECLARE @firstfive varchar(10)

DECLARE @lastfour varchar(10)

DECLARE @city varchar(max)

--

OPEN XferCursor

FETCH NEXT FROM XferCursor INTO @Add2, @Add3, @Add4, @Add5

WHILE @@FETCH_STATUS = 0

BEGIN

SET @commalocation = 0

SET @add3size = LEN(@Add3)

SET @zipnums = 0

SET @commalocation = CHARINDEX(',', @add3)

SET @zipnums = dbo.ExtractInteger(@add3)

IF (LEN(@zipnums) > 5)

    BEGIN

    SET @firstfive = SUBSTRING(@zipnums, 0, 6)

    SET @lastfour = SUBSTRING(@zipnums, 4, 4)

    SET @zipnums = @firstfive + '-' + @lastfour

    END

SET @city = SUBSTRING(@add3, 0, @commalocation)

PRINT(@city + '--' + @zipnums)

UPDATE QB

SET [Address 3] = @city, [Address 5] = @zipnums

WHERE CURRENT of XferCursor

FETCH NEXT FROM XferCursor INTO @Add2, @Add3, @Add4, @Add5

END

CLOSE XferCursor

DEALLOCATE XferCursor