sorting address field by its component parts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sorting address field by its component parts

Author

Topic

songster1101
Starting Member

2 Posts

Posted - 2007-11-14 : 08:10:36

The file of about 30,000 records contains an address field where addresses are entered in the following form:

128 NW 8 ST
3444 SW 19 AVE
32 SE 122 TERR
12604 SW 148 PL

Then there are anomalies such as
123 E BLVD
(I can ignore the anomalies until the main challenge is overcome.)

The challenge is that I have been asked to sort the addresses in the following order:

quadrant (e.g., NW, SW),
street number (e.g., 122),
street name (e.g., TERR)
house number (e.g., 128)

It seemed like I should create a view that can be updated regularly, as records will be added/deleted ongoing.

How do I get these part into a four-column view? Or, is there another way to do this?

Esther

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 08:25:57

[code]DECLARE @Sample TABLE (Adr VARCHAR(600))

INSERT @Sample
SELECT '128 NW 8 ST' UNION ALL
SELECT '3444 SW 19 AVE' UNION ALL
SELECT '32 SE 122 TERR' UNION ALL
SELECT '12604 SW 148 PL'

SELECT HouseNumber,
Quadrant,
StreetNumber,
StreetName
FROM (
SELECT PARSENAME(a, 4) AS HouseNumber,
PARSENAME(a, 3) AS Quadrant,
PARSENAME(a, 2) AS StreetNumber,
PARSENAME(a, 1) AS StreetName
FROM (
SELECT REPLACE(Adr, ' ', '.') AS a
FROM @Sample
) AS d
) AS d
ORDER BY Quadrant,
StreetNumber,
StreetName,
HouseNumber[/code]

E 12°55'05.25"
N 56°04'39.16"

songster1101
Starting Member

2 Posts

Posted - 2007-11-14 : 08:53:19

My challenge is overcome, thanks to Peso.
I've been migrated from SAS to SQL, and have only been doing SQL a couple of weeks. This forum and information is invaluable to my situation.

Esther

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources