Friday, November 30, 2012 11:37 PM
I need to sort alphanumeric street numbers, which can be 1, 23, A52, 82 1/2, C, 35H. I would like to sort them to according to the number part: 1, 23, 35H, A52, 82 1/2, C.
Is there a way I can sort them? Yet, fast? Thanks!
Saturday, December 01, 2012 12:59 AM
First, you need to parse these numbers and divide it into Number Prefix, Number and then Number Postfix.
Preferably do it during data entry or data load and keep theses parts as separate columns.
Then just sort by the Number column.
Adres parsing is not trivial and depends on the country the address belong to.
Saturday, December 01, 2012 8:11 AMModerator
You can use CASE expression in ORDER BY for complex sort:
Saturday, December 01, 2012 7:00 PM
Look up the CASS standards from the USPS. Then get a tool that correctly format your addresses. Now your cleaned up dat a will sort easily and correctly for mail delivery. Why re-invent the wheel?
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
Sunday, December 02, 2012 2:10 AM
You need soundex. I demonstrate some tools you can use to split your address out into useful substrings. I split of the "number" which is PO in the case of PO boxes and the street I put a soundex on. Now I can sort on the street number alone.
USE Experiment; GO IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1; GO CREATE TABLE t1 ( c1 INT IDENTITY(1,1), c2 VARCHAR(128), c3 AS LTRIM(RTRIM(SUBSTRING(c2, 1,CHARINDEX(' ',c2)))),--probably a number c4 AS SOUNDEX(LTRIM(RTRIM(REPLACE(SUBSTRING(c2,CHARINDEX(' ',c2),LEN(c2)-CHARINDEX(' ',c2)),' ',''))))--probably beginning with words ) INSERT INTO t1 (c2) VALUES ('123 Main St Suite 2'), ('1178 Pi Meson Court'), ('PO Box Handle Me with a Case after applying CASS Standards'), ('07987 Make sure your zip codes aren''t losing their leading zeroes there''s a lot of people in the Northeast'); GO IF OBJECT_ID('idx1') IS NOT NULL DROP INDEX t1.idx1; CREATE INDEX idx1 ON t1 (c4) INCLUDE (c3); SELECT * FROM t1
If you're happy and you know it vote and mark.