January 27, 2010 at 9:13 am
I have an issue with the code below not parsing anything on E ST correctly for example: 2323 E ST. I know why it does not as it is assuming that E is a direction and not a street but I have no idea how to exclude E Street from the direction statement or how to change the query to reflect that E Street is indeed a street and not a direction.
Essentially I need to take 2323 E ST (loc) and have it look like streetNum - 2323, Direction - NULL, StreetName - E but if the address looks like 1234 W CHESNUT ST it needs to reflect the direction (W) in field Direction.
Also the code below is also excluding numbered Avenues for example 2323 78TH AVE. Please note that it works just fine for 2323 02ND ST.
Any help with resolving this problem would be greatly appreciated.
WITH Addresses AS
(SELECT Parcel, streetNameDir Addr, PATINDEX('%[ ][ENSW][ ]%',streetNameDir) AS dpos,
CHARINDEX(' ',streetNameDir) AS spos
FROM dbo.Prop)
UPDATE p SET streetNum =
CASE
WHEN a.Addr LIKE '[1-9][^A-Z]%[ ]%'
THEN LEFT(a.Addr,a.spos-1)
ELSE ''
END,
Direction =
CASE a.dpos
WHEN 0
THEN
CASE
WHEN a.Addr LIKE '[ENSW][ ]%'
THEN LEFT(a.Addr,1)
ELSE ''
END
ELSE SUBSTRING(a.Addr,a.dpos+1,1)
END,
streetName =
CASE a.dpos
WHEN 0
THEN
CASE
WHEN a.Addr LIKE '[1-9][^A-Z]%[ ]%' OR a.Addr LIKE '[ENSW][ ]%'
THEN STUFF(a.Addr,1,a.spos,'')
ELSE a.Addr
END
ELSE STUFF(a.Addr,1,a.dpos+2,'')
END
FROM dbo.Prop p JOIN Addresses a ON p.Parcel = a.Parcel;
Thank you,
George Greiner
January 27, 2010 at 9:55 am
How about the following code?
It is based on the assumption that, if there is another blank character after the one that follows the direction separator [dpos], then dpos is a "true direction", otherwise it will be ignored.
If my assumption is incorrect, please provide the business rules to differentiate between a direction and a non-direction character.
DECLARE @t TABLE (addr VARCHAR(30))
INSERT INTO @t
SELECT '2323 E ave ST' UNION ALL
SELECT '2323 E ST' UNION ALL
SELECT '1234 W CHESNUT ST' UNION ALL
SELECT '2323 78TH AVE' UNION ALL
SELECT '2323 02ND ST'
;WITH Addresses AS
(SELECT a.Addr Addr, PATINDEX('%[ ][ENSW][ ]%',a.Addr) AS dpos,
CHARINDEX(' ',a.Addr) AS spos,
CHARINDEX(' ',a.Addr,
CASE
WHEN PATINDEX('%[ ][ENSW][ ]%',a.Addr) >0
THEN PATINDEX('%[ ][ENSW][ ]%',a.Addr)+3
ELSE LEN(a.Addr)
END) AS spos2
FROM @t a)
SELECT *,
CASE
WHEN a.Addr LIKE '[1-9][^A-Z]%[ ]%'
THEN LEFT(a.Addr,a.spos-1)
ELSE ''
END AS t,
dpos,
spos,
Direction =
CASE a.dpos
WHEN 0
THEN
CASE
WHEN a.Addr LIKE '[ENSW][ ]%'
THEN LEFT(a.Addr,1)
ELSE ''
END
ELSE
CASE
WHEN spos2>0
THEN SUBSTRING(a.Addr,a.dpos+1,1)
ELSE ''
END
END,
streetName =
CASE a.spos2--a.dpos
WHEN 0
THEN
CASE
WHEN a.Addr LIKE '[1-9][^A-Z]%[ ]%' OR a.Addr LIKE '[ENSW][ ]%'
THEN STUFF(a.Addr,1,a.spos,'')
ELSE a.Addr
END
ELSE STUFF(a.Addr,1,a.dpos+2,'')
END
FROM Addresses a
January 27, 2010 at 10:42 am
I have run this against various addresses and everything works great! I cannot seem to convert it to an update query though and get it to function correctly. Could you by chance change that to an update query as I cannot quite figure out what I am doing wrong.
Thank you very much for your help!
January 27, 2010 at 10:53 am
You need to link the CTE back to your original table. Since I don't have any other field unique field I used the addr field. I expect you have an identity column you can include into the CTE and join on that.
DECLARE @t TABLE (addr VARCHAR(30), direction CHAR(1), streetname VARCHAR(50))
INSERT INTO @t (addr)
SELECT '2323 E ave ST' UNION ALL
SELECT '2323 E ST' UNION ALL
SELECT '1234 W CHESNUT ST' UNION ALL
SELECT '2323 78TH AVE' UNION ALL
SELECT '2323 02ND ST'
;WITH Addresses AS
(SELECT a.Addr Addr, PATINDEX('%[ ][ENSW][ ]%',a.Addr) AS dpos,
CHARINDEX(' ',a.Addr) AS spos,
CHARINDEX(' ',a.Addr,
CASE
WHEN PATINDEX('%[ ][ENSW][ ]%',a.Addr) >0
THEN PATINDEX('%[ ][ENSW][ ]%',a.Addr)+3
ELSE LEN(a.Addr)
END) AS spos2
FROM @t a)
UPDATE t
SET
direction =
CASE a.dpos
WHEN 0
THEN
CASE
WHEN a.Addr LIKE '[ENSW][ ]%'
THEN LEFT(a.Addr,1)
ELSE ''
END
ELSE
CASE
WHEN spos2>0
THEN SUBSTRING(a.Addr,a.dpos+1,1)
ELSE ''
END
END,
streetName =
CASE a.spos2--a.dpos
WHEN 0
THEN
CASE
WHEN a.Addr LIKE '[1-9][^A-Z]%[ ]%' OR a.Addr LIKE '[ENSW][ ]%'
THEN STUFF(a.Addr,1,a.spos,'')
ELSE a.Addr
END
ELSE STUFF(a.Addr,1,a.dpos+2,'')
END
FROM Addresses a
INNER JOIN @t t ON a.addr =t.addr
SELECT *
FROM @t
January 27, 2010 at 12:42 pm
Thanks again! I modified it and made it work and it actually fixed my other problem regarding the Avenues such as 73rd Avenue etc etc.
January 27, 2010 at 12:54 pm
Glad it worked. 😀
Btw: Did you notice how I "translated" your "descriptive sample data" into ready to use SQL code?
If you provide data in such a format you'll increase the number of people taking the time to look at your issue. Therewith, you may increase the number of solutions you'll get but definitely you'll reduce the time until you get your first response... 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply