Sorting: Text with embedded number

[Gary Schuldt]

There must be an easy solution to this problem, which I've seen before but
don't know a solution:

I have a text field which represents a geographical sector Sector followed
by a sequence number. Examples are: NE-1, SE-10, NW-6, etc.

When I sort Sector ascending in a query, of course the order comes out:

NE-1
NE-10
NE-11
NE-2
etc.

when I want it to come out

NE-1
NE-2
NE-10
NE-11
etc.

What's the easiest way to get the result I want?

Thanks.

Gary Schuldt

 

[Bas Cost Budde]

There must be an easy solution to this problem, which I've seen before but
don't know a solution:

I have a text field which represents a geographical sector Sector followed
by a sequence number. Examples are: NE-1, SE-10, NW-6, etc.

What's the easiest way to get the result I want?

If the 'NE' part is fixed in length, you can add an expression to your
query:
val(mid(sector,4))

and sort on this (if you forget the Val, the result doesn't change from
your problem)

If the text part is of varying length, it will get difficult.

 

[Gary Schuldt]

Sounds promising. You mean, replace the sort on Sector with two expressions
and sort on them--i.e., primary sort on 1st two characters of Sector and a
secondary on the numeric representation of the last part
(val(mid(sector,4)) )?

Gary

 

[Bas Cost Budde]

Sounds promising. You mean, replace the sort on Sector with two expressions
and sort on them--i.e., primary sort on 1st two characters of Sector and a
secondary on the numeric representation of the last part
(val(mid(sector,4)) )?

Yes, indeed. The first expression you don't need to change--it will sort
on the first characters anyway.
-bcb

 

[Gary Schuldt]

Right! Thanks for the help!

Gary

Yes, indeed. The first expression you don't need to change--it will sort
on the first characters anyway.
-bcb