Splitting an address into fields in SQL

Question

I have the following code to split an address string in T-SQL. Excepting the unit number, which is already in its own field, it would affect too much of the application to split the address into different database fields. It's a bit of a long story, but it's also not possible to make it a parameterized query, or to write the equivalent application code in C#. Still, I'm curious if anyone can think of any edge case that would put things into the wrong fields.

The only case I've come across is "East End Ave", but even the US Postal Service suggests incorrectly parsing 'East' as a directional (for consistency) (reference: very long pdf, search for 'directional', or go to page 233).

I'm only worried about Canadian and American addresses, but in Canada, French-style street types before the street name are fair game (e.g. 'Rue Montreal').

StreetType and StreetDirection are tables which contain street types and street directions, as TypeName/TypeAbbr and DirectionName/DirectionType pairs respectively - I can post the values I've been using there if anyone's curious, but the StreetType is quite long. StreetDirection also includes French directions ('ouest', 'nord', 'sud', 'est', etc.), as well as their equivalents of 'northeast', 'northwest', etc.

DECLARE @CivicNum AS NVARCHAR(10) = ''
DECLARE @Predir AS NVARCHAR(10) = ''
DECLARE @Name AS NVARCHAR(100) = ''
DECLARE @Type AS NVARCHAR(15) = ''
DECLARE @FrenchType AS NVARCHAR(15) = ''
DECLARE @Postdir AS NVARCHAR(10) = ''

SET @Name = '123 Fake St.'

SET @CivicNum = SUBSTRING(@Name, 1, 1)

IF ISNUMERIC(@CivicNum) = 1
  BEGIN
    -- will assume a civic number
    DECLARE @LastSpace AS INTEGER
    DECLARE @NextSpace AS INTEGER

    DECLARE @Temp as VARCHAR(15)

    -- first token - civic number
    SET @LastSpace = 1
    SET @NextSpace = CHARINDEX(' ', @Name)
    SET @CivicNum = SUBSTRING(@Name, 1, @NextSpace)

    -- next token - check for fraction
    SET @LastSpace = @NextSpace + 1
    SET @NextSpace = CHARINDEX(' ', @Name, @LastSpace)

    SET @Temp = SUBSTRING(@Name, @LastSpace, @NextSpace - @LastSpace)
    IF CHARINDEX('\\', @Temp) > 0 OR CHARINDEX ('/', @Temp) > 0
      SET @CivicNum = @CivicNum + ' ' + @Temp
    ELSE
      SET @NextSpace = @LastSpace

    -- reverse to extract type, direction from right
    SET @Name = REVERSE(SUBSTRING(@Name, @NextSpace, LEN(@Name)))

    -- last token - post direction
    SET @LastSpace = 1
    SET @NextSpace = CHARINDEX(' ', @Name)    
    SET @Temp = RTRIM(LTRIM(REVERSE(SUBSTRING(@Name, 1, @NextSpace))))
    IF SUBSTRING(@Temp, LEN(@Temp), 1) = '.' -- strip period
      SET @Temp = SUBSTRING(@Temp, 1, LEN(@Temp) - 1)

    SELECT @PostDir=DirectionAbbr FROM StreetDirection WHERE UPPER(DirectionName) LIKE UPPER(@Temp)
    IF NOT (@PostDir LIKE @Temp) -- try abbreviation
      SELECT @PostDir=DirectionAbbr FROM StreetDirection WHERE UPPER(DirectionAbbr) LIKE UPPER(@Temp)

    -- get next token if there was a postdir
    IF NOT (@PostDir LIKE '') 
      BEGIN
       SET @LastSpace = @NextSpace + 1
       SET @NextSpace = CHARINDEX(' ', @Name, @LastSpace + 1)
       SET @Temp = RTRIM(LTRIM(REVERSE(SUBSTRING(@Name, @LastSpace, @NextSpace - @LastSpace))))
       IF SUBSTRING(@Temp, LEN(@Temp), 1) = '.' -- strip period 
          SET @Temp = SUBSTRING(@Temp, 1, LEN(@Temp) - 1)
      END

    -- next-to-last token - street type      
    SELECT @Type=TypeName FROM StreetType WHERE UPPER(TypeName) LIKE UPPER(@Temp)
    IF NOT (@Type LIKE @Temp) -- try abbreviation
      SELECT @Type=TypeName FROM StreetType WHERE UPPER(TypeAbbr) LIKE UPPER(@Temp)

    -- reverse address again to check for pre-dir and french street type
    IF NOT (@Type LIKE '')
      SET @LastSpace = @NextSpace + 1

    SET @Name = RTRIM(LTRIM(REVERSE(SUBSTRING(@Name, @LastSpace, LEN(@Name)))))

    -- next token - pre-direction
    SET @LastSpace = 1
    SET @NextSpace = CHARINDEX(' ', @Name)
    SET @Temp = RTRIM(LTRIM(SUBSTRING(@Name, 1, @NextSpace)))
    IF SUBSTRING(@Temp, LEN(@Temp), 1) = '.' -- strip period
      SET @Temp = SUBSTRING(@Temp, 1, LEN(@Temp) - 1)

    SELECT @PreDir=DirectionAbbr FROM StreetDirection WHERE UPPER(DirectionName) LIKE UPPER(@Temp)
    IF NOT (@PreDir LIKE @Temp) -- try abbreviation
      SELECT @PreDir=DirectionAbbr FROM StreetDirection WHERE UPPER(DirectionAbbr) LIKE UPPER(@Temp)    

    -- next token - French street type
    IF NOT (@PreDir LIKE '') 
      BEGIN
       SET @LastSpace = @NextSpace + 1
       SET @NextSpace = CHARINDEX(' ', @Name, @LastSpace + 1)
       SET @Temp = RTRIM(LTRIM(SUBSTRING(@Name, @LastSpace, LEN(@Name))))
       IF SUBSTRING(@Temp, LEN(@Temp), 1) = '.' -- strip period 
          SET @Temp = SUBSTRING(@Temp, 1, LEN(@Temp) - 1)
      END

    -- check for French street type before address
    IF (@Type LIKE '') -- only if not already found (prefer English type)
      BEGIN
        SELECT @FrenchType=TypeName FROM StreetType WHERE UPPER(TypeName) LIKE UPPER(@Temp)
        IF NOT (@FrenchType LIKE @Temp) -- try abbreviation
          SELECT @FrenchType=TypeName FROM StreetType WHERE UPPER(TypeAbbr) LIKE UPPER(@Temp)
      END

    IF NOT (@FrenchType Like '')
      BEGIN
        SET @LastSpace = @NextSpace
        SET @Type = @FrenchType
      END

    -- rest is street name
    SET @Name = SUBSTRING(@Name, @LastSpace, LEN(@Name))

    SELECT @CivicNum AS CivicNum, @Name AS Address, @Type AS StreetType, @PostDir + @PreDir AS Direction
ELSE
    SELECT @CivicNum AS CivicNum, @Name AS Address, @Type AS StreetType, @PostDir + @PreDir AS Direction

If nothing else, I hope this is a snippet of code someone finds handy!