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'). For a tie-breaker, I default to the English style (e.g. 'Rue Street' is parsed with the name 'Rue' and type 'Street').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 E Fake St. S'SET @CivicNum = SUBSTRING(@Name, 1, 1)IF ISNUMERIC(@CivicNum) = 1  BEGIN    -- will assume an initial integer is a civic number    DECLARE @LastSpace AS INTEGER    DECLARE @NextSpace AS INTEGER    DECLARE @Temp as VARCHAR(15)    -- get the first token, and push it into @CivicNum    SET @LastSpace = 1    SET @NextSpace = CHARINDEX(' ', @Name)    SET @CivicNum = SUBSTRING(@Name, 1, @NextSpace)    -- get the next token, to check for a fractional @CivicNum    SET @LastSpace = @NextSpace + 1    SET @NextSpace = CHARINDEX(' ', @Name, @LastSpace)    SET @Temp = SUBSTRING(@Name, @LastSpace, @NextSpace - @LastSpace)    -- assumption: if the token contains a slash, it is a fraction    -- for @CivicNum    IF CHARINDEX('\\', @Temp) > 0 OR CHARINDEX ('/', @Temp) > 0      SET @CivicNum = @CivicNum + ' ' + @Temp    ELSE      SET @NextSpace = @LastSpace    -- reverse the string to extract type, direction from right    SET @Name = REVERSE(SUBSTRING(@Name, @NextSpace, LEN(@Name)))    -- get the final token, check if it is a post directional (N, S, E, W)    SET @LastSpace = 1    SET @NextSpace = CHARINDEX(' ', @Name)        SET @Temp = RTRIM(LTRIM(REVERSE(SUBSTRING(@Name, 1, @NextSpace))))    -- strip period, if one exists    IF SUBSTRING(@Temp, LEN(@Temp), 1) = '.'       SET @Temp = SUBSTRING(@Temp, 1, LEN(@Temp) - 1)    -- check if it exists in the StreetDirection table    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)    -- if @PostDir was found, get the next token    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    -- check if the current token is a StreetType        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-directional and french street type    IF NOT (@Type LIKE '')      SET @LastSpace = @NextSpace + 1    SET @Name = RTRIM(LTRIM(REVERSE(SUBSTRING(@Name, @LastSpace, LEN(@Name)))))    -- get the next token, and check for a pre-directional    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)        -- if a pre-directional was found, get the next token    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 an    -- English street type has not already been found    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 @FrenchType was found, get the final remaining token    IF NOT (@FrenchType Like '')      BEGIN        SET @LastSpace = @NextSpace        SET @Type = @FrenchType      END    -- assume it is the street name    SET @Name = SUBSTRING(@Name, @LastSpace, LEN(@Name))    SELECT @CivicNum AS CivicNum, @Name AS Address, @Type AS StreetType, @PostDir + @PreDir AS Direction  ENDELSE    SELECT @CivicNum AS CivicNum, @Name AS Address, @Type AS StreetType, @PostDir + @PreDir AS DirectionIf nothing else, I hope this is a snippet of code someone finds handy! Although they don't particularly apply in my case, here is a handy list of address edge cases from @200_success.
Splitting an address into fields in SQL
sql;parsing;sql server;t sql