StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The reliable way to do it (reliable in terms of "to sort your data correctly", not "to solve your general problem") is to split the data into street name and house number and sort both of them on their own. But this requires knowing where the house number starts. And this is the tricky part - making the assumption best fits your data.</p> <p>You should use something like the following to refactor your data and from now on store the house number in a separate field. All this string-juggling won't perform too well when it comes to sorting large data sets.</p> <p>Assuming it is the last thing in the street name, and it contains a number:</p> <pre><code>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 </code></pre> <p>This assumes these conditions:</p> <ul> <li>a street address <em>can</em> have a house number</li> <li>a house number <em>must</em> be the last thing in a street address (no "525 Monroe Av.")</li> <li>a house number <em>should</em> start with a digit to be sorted correctly</li> <li>a house number <em>can</em> be a range ("200-205"), this would be sorted below 200</li> <li>a house number <em>must not</em> contain spaces or recognition fails (When you look at your data, you could apply something like <code>REPLACE(street, ' - ', '-')</code> to sanitize common patterns beforehand.)</li> <li>the whole thing is still an approximation that certainly deviates from what it would look like in a telephone book, for example</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload