Clever Geek Handbook

SQL Query (Ordered)

I want to list (sorted list) all my entries from streetNames attribute in my Customers table / relation. eg. I want to achieve the following order:

Street_1A
Street_1B
Street_2A
Street_2B
Street_12A
Street_12B

Simple ordering with streetNames will do a lexical comparison, and then Street_12A and B will come up to Street_2A / B and that's not true. Is it possible to solve this with pure SQL?

+1


source to share


7 replies


The reliable way to do this (reliable in terms of "sorting your data correctly" rather than "solving your common problem") is to split the data into street name and house number and sort both of them yourself, but that requires knowing where the number starts at home. And this is the tricky part - this guess is the best fit for your data.

To refactor your data, you should use something like the following to store the house number in a separate field. All this string juggling won't work too well when it comes to sorting large datasets.

Assuming it's the last thing in the street name and it contains a number:



DECLARE @test TABLE
(
  street VARCHAR(100)
)

INSERT INTO @test (street) VALUES('Street')
INSERT INTO @test (street) VALUES('Street 1A')
INSERT INTO @test (street) VALUES('Street1 12B')
INSERT INTO @test (street) VALUES('Street 22A')
INSERT INTO @test (street) VALUES('Street1 200B-8a')
INSERT INTO @test (street) VALUES('')
INSERT INTO @test (street) VALUES(NULL)

SELECT
  street,
  CASE 
    WHEN LEN(street) > 0 AND CHARINDEX(' ', REVERSE(street)) > 0
    THEN CASE
      WHEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1) LIKE '%[0-9]%'
      THEN LEFT(street, LEN(street) - CHARINDEX(' ', REVERSE(street)))
    END
  END street_part,
  CASE 
    WHEN LEN(street) > 0 AND CHARINDEX(' ', REVERSE(street)) > 0
    THEN CASE 
      WHEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1) LIKE '%[0-9]%'
      THEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1)
    END
  END house_part,
  CASE 
    WHEN LEN(street) > 0 AND CHARINDEX(' ', REVERSE(street)) > 0
    THEN CASE 
      WHEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1) LIKE '%[0-9]%'
      THEN CASE
        WHEN PATINDEX('%[a-z]%', LOWER(RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1))) > 0
        THEN CONVERT(INT, LEFT(RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1), PATINDEX('%[^0-9]%', LOWER(RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1))) - 1))
      END
    END
  END house_part_num
FROM
  @test 
ORDER BY
  street_part,
  house_part_num,
  house_part

This assumes the following conditions:

  • Street may contain house number
  • the house number must be the last on the street address (no "525 Monroe Av.")
  • the house number must start with a digit, which must be correctly sorted.
  • house number can be a range ("200-205"), this will be sorted below 200
  • the house number must not contain spaces or the recognition fails (when you look at your data, you can use something like REPLACE(street, ' - ', '-')

    to pre-clear common patterns beforehand.)
  • this is all still an approximation, which is definitely different from what it would look like in the phone book like
-2


source


Select street name from tablex order by udf_getStreetNumber (street_name)

in your udf_getStreetNumber - write your business rule to remove the number



EDIT

I think you can now use the regex functions in SQL Server. I would just strip out all non-null characters from the input.

+2


source


For the record: It's called Natural Sort Order , and there is a Coding Horror Article in the topic.

I think you can do this in SQL using some of the code shown here, but it will always be on a case-by-case basis.

+2


source


I'm sure you could, by dividing the streetName field into different parts with something like substr (streetName, 1, find ("", streetName)) for street only, and so on. But it will be pretty messy and he will have to deal with all kinds of special cases (house number, no add house number) or international issues (in the US, addresses are usually like 1 street).

But if you want to sort as you described, and this is an important requirement, it would be better to model you streetName in three parts: street (for example "Street"), house_number (for example, 1, 2, 12), house_num_addition (for example, " A "," B "). Then sorting becomes trivial in SQL.

+1


source


If you have write access to the database I would recommend converting the whole thing to use 3 separate fields and then using them appropriately. So you can even do it in PHP (yes, it will take a while, but it will only happen once). It can be a bit of a pain if you have a large codebase that needs to check all queries against this table, but it will eventually pay off later. For example, this will make it easier to search by address.

+1


source


Yes it is possible! But definitely no interest! If you find that someone here is willing to spend a few hours writing and testing an SP that will split your street names into a combination of streetName + streetNumber, give me your name: I'll give him a few problems when I thought I should pay to get work done.

By the way, can't you split your data into 2 fields, one "street name" with only the street name and a new "buildingNumber" field? (Avoid calling it "streetNumber" as some countries / cities have numbers on the streets).

-1


source


If so, all values ​​in the streetNames column follow the pattern StreetName-space - StreetNumber

where StreetName may contain other spaces, but StreetNumber CAN NOT, then this will work:

Declare @T Table (streetName VarChar(50))
Insert @T(streetName) Values('Street 1A')
Insert @T(streetName) Values('Street 2A')
Insert @T(streetName) Values('Street 2B')
Insert @T(streetName) Values('Street 12A')
Insert @T(streetName) Values('Another Street 1A')
Insert @T(streetName) Values('Another Street 4A')
Insert @T(streetName) Values('a third Street 12B')
Insert @T(streetName) Values('a third Street 1C')

Select * From @T 
Order By Substring(StreetName, 0, 1 + len(StreetName) - charIndex(' ', reverse(StreetName))),
       Cast(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)),  
        Case When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 5)) = 1  Then 5
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 4)) = 1  Then 4
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 3)) = 1  Then 3
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 2)) = 1  Then 2
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 1)) = 1  Then 1
                End) as Integer),
        Substring(StreetName, len(StreetName) - charIndex(' ', reverse(StreetName)) +
            Case When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 5)) = 1  Then 5
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 4)) = 1  Then 6
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 3)) = 1  Then 5
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 2)) = 1  Then 4
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 1)) = 1  Then 3
                End, Len(StreetName))
-1


source






More articles:


All Articles