Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
how to sort alphanumberic street numbers

Answered how to sort alphanumberic street numbers

  • Friday, November 30, 2012 11:37 PM
     
     
    Sign In to Vote
    0

    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!





     

All Replies

  • Saturday, December 01, 2012 12:59 AM
     
     Answered
    Sign In to Vote
    1

    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.

    http://stackoverflow.com/questions/16413/parse-usable-street-address-city-state-zip-from-a-string

     
  • Saturday, December 01, 2012 8:11 AM
    Moderator
     
     
    Sign In to Vote
    0

    You can use CASE expression in ORDER BY for complex sort:

    http://www.sqlusa.com/bestpractices2005/orderbycase/

    http://www.sqlusa.com/bestpractices/training/scripts/casefunction/

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

     
  • Saturday, December 01, 2012 7:00 PM
     
     
    Sign In to Vote
    1

    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
     
      Has Code
    Sign In to Vote
    0

    Hi,

    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.