Need to sort Numbered Streets in Order

Question

Post reply

Need to sort Numbered Streets in Order

Kirk Hayer-285823

Valued Member

Points: 64
More actions
May 24, 2006 at 9:32 am

#168500

I have a column with street names which contains numbered streets and I need to have them sorted in order by number. Example of street names:
E 101TH , E 48TH, E 92ND, E 88TH, E 56TH, etc.
The sorted order should be:
E 48TH, E 56TH, E 88TH, E 92ND, E 101TH, etc.
Thanks, Kirk

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Add the Order By like this if you know that the number starts at the third character:

Order By substring(MyField,3,20)

If you don't know exactly how many letters are there before a number but always have a space before number, do

Order by substring(MyField,patindex('% %',MyField)+1,20)

The syntax for patindex is patindex('%pattern%', expression). In your case a pattern is a space

Regards,Yelena Varsha

Kirk Hayer-285823 Valued Member Points: 64 More actions · Answer 2

Yelena, Tried your suggestion: The sorts still places the street names that are 100 or above before the streets that are less than 100--

E 100TH AVE

E 100TH CT

E 100TH DR

E 100TH LN

E 100TH PL

E 100TH WAY

E 101ST AVE

E 101ST PL

E 101ST WAY

E 102ND AVE

E 102ND PL

E 104TH AVE

... etc., all the way to ...

E 120TH AVE

E 121ST CIR

E 123RD AVE

E 124TH AVE

E 48TH AVE ... and then the streets less than 99 start

E 49TH DR

E 50TH AVE

E 52ND AVE

E 52ND PL

... etc., all the way to ...

E 98TH AVE

E 98TH PL

E 98TH WAY

E 99TH AVE

E 99TH PL

E 99TH WAY

Any other ideas? -Kirk

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

I understand. You are asking about numbers as numbers, not a dictionary order. Then if you know your number as a string format you may convert string to an integer and then sort. You still have to use substring or patindex functions to separate the number part of the string and then use cast or convert functions to convert to a number.

Something like that:

select convert(integer,substring(@a,patindex('% %',@a)+1,patindex('%[a-z]%',substring(@a,3,20))-1))

then instead of Select you would order by this expression where @a is your field. I just tested on a local variable, that is why I named it @a. Basically you are converting to integer the substring that starts right after the space and has as many characters as a position of the first letter if we start counting after the space deducted 1

The following will make you better understand what each part of this expression does:

declare @a varchar(20)

select @a = 'E 49th way'

select convert(integer,substring(@a,patindex('% %',@a)+1,patindex('%[a-z]%',substring(@a,3,20))-1))

select substring(@a,3,20)

select patindex('%[a-z]%',substring(@a,3,20))-1

select substring(@a,patindex('% %',@a)+1,patindex('%[a-z]%',substring(@a,3,20))-1)

I do admit that other developers may have a more efficient way to parse the expression and convert to integer. For example you probably may use ISNUMERIC function to evaluate if each character is a number.

I am not also sure if I have to consider if they are upper or lower case letters, you may want to convert to the same case before the processing. Test anyway.

Regards,Yelena Varsha

stax68 SSChampion Points: 11711 More actions · Answer 4

A slight variant on the above that will extract a single block of numerals (<256) embedded in any string.

Incidentally, the clumsiness of this approach highlights the fact that strictly speaking this address data is not normalised (not in '1st normal form') as individual data values can be decomposed into independent meaningful parts, the East part. the 102(ND) part, and the AVE part.

Decomposing the data in this way would mean, for example, you could conceivably write a TSQL function to generate directions from a to b in 'N blocks West then M blocks North' style, or order addresses from North to South for deliveries; the possibilities are endless. Of course it might not be practical for any one of a thousand reasons...

set

numeric_roundabort off

go

if

exists(select * from sysobjects where xtype = 'U' and name = 'addr')

drop

table addr

go

create

table addr

(

addr varchar(14)

,streetno as cast(substring(left(addr,patindex('%[0-9][^0-9]%',addr))

,patindex('%[^0-9][0-9]%',addr)+1,14))as tinyint))

go

create

clustered index ix on addr(streetno, addr)

go

insert

addr(addr)

select

'E 100TH AVE' union all select 'E 100TH CT' union all select 'E 100TH DR' union all

select

'E 100TH LN' union all select 'E 100TH PL' union all select 'E 100TH WAY' union all

select

'E 101ST AVE' union all select 'E 101ST PL' union all select 'E 101ST WAY' union all

select

'E 102ND AVE' union all select 'E 102ND PL' union all select 'E 104TH AVE' union all

select

'E 120TH AVE' union all select 'E 121ST CIR' union all select 'E 123RD AVE' union all

select

'E 124TH AVE' union all select 'E 48TH AVE' union all select 'E 49TH DR' union all

select

'E 50TH AVE' union all select 'E 52ND AVE' union all select 'E 52ND PL' union all

select

'E 98TH AVE' union all select 'E 98TH PL' union all select 'E 98TH WAY' union all

select

'E 99TH AVE' union all select 'E 99TH PL' union all select 'E 99TH WAY'

go

select

addr from addr order by streetno

go

drop table addr

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant