Skip to main content
Code Review

Return to Question

replaced http://codereview.stackexchange.com/ with https://codereview.stackexchange.com/
Source Link

If 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 @200_success.

If 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.

If 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.

more descriptive comments
Source Link
Hannele
  • 171
  • 1
  • 5
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 civicpush numberit into @CivicNum
 SET @LastSpace = 1
 SET @NextSpace = CHARINDEX(' ', @Name)
 SET @CivicNum = SUBSTRING(@Name, 1, @NextSpace)
 
 -- get the next token, -to check for fractiona 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)))
 
 -- lastget the final token, -check if it is a post directiondirectional (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) = '.' -- strip period
 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)
 
 -- getif next@PostDir tokenwas iffound, thereget wasthe anext postdirtoken
 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-lastcheck tokenif -the streetcurrent typetoken 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-dirdirectional 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-directiondirectional
 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) 
 
 -- nextif tokena pre-directional Frenchwas streetfound, typeget 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
 
 -- restassume 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
 END
ELSE
 SELECT @CivicNum AS CivicNum, @Name AS Address, @Type AS StreetType, @PostDir + @PreDir AS Direction

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
 END
ELSE
 SELECT @CivicNum AS CivicNum, @Name AS Address, @Type AS StreetType, @PostDir + @PreDir AS Direction

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
 END
ELSE
 SELECT @CivicNum AS CivicNum, @Name AS Address, @Type AS StreetType, @PostDir + @PreDir AS Direction
added 6 characters in body; added 4 characters in body
Source Link
Hannele
  • 171
  • 1
  • 5
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
 END
ELSE
 SELECT @CivicNum AS CivicNum, @Name AS Address, @Type AS StreetType, @PostDir + @PreDir AS Direction

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

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
 END
ELSE
 SELECT @CivicNum AS CivicNum, @Name AS Address, @Type AS StreetType, @PostDir + @PreDir AS Direction
added link to edge cases
Source Link
Hannele
  • 171
  • 1
  • 5
Loading
Tweeted twitter.com/#!/StackCodeReview/status/401560725627629568
Loading
improved formatting (escaped backslash)
Source Link
Hannele
  • 171
  • 1
  • 5
Loading
Source Link
Hannele
  • 171
  • 1
  • 5
Loading
lang-sql

AltStyle によって変換されたページ (->オリジナル) /