SQL Sorting of strings containing a numeric component - %MVR

Caché, SQL

It is often necessary to sort the results of a query on a string field containing a combination of alphabetic and numeric characters. In cases like this the default string collation may not always return the data in the expected sequence.

 

An example of this may be where a select from Samples.Person should order the results by the street address, but firstly ordered by the street number part as numeric, and then by the street name.

 

The default query will return the results as follows:

 

SELECT Name,Home_Street

FROM Sample.Person

ORDER BY Home_Street

 

NameHome_Street
Eastman,Richard W.1031 Madison Drive
Quine,Hannah O.1039 Washington Blvd
Hills,Wolfgang J.1100 Elm Avenue
Baker,Molly O.1100 First Street
Edison,Terry T.1113 Ash Street
Willeke,Aviel R.1144 Oak Place
Eisenstien,Neil E.1313 First Blvd
Edison,Edgar O.1320 Main Drive
Zimmerman,Terry S.1332 First Blvd
Tillem,Rhonda T.1337 Maple Blvd
Novello,Tara D.137 Franklin Place

 

Our requirement however is that 137 Franklin Place should be returned fist, because the numeric value of the street number is the smallest.

 

A simple solution for this will be to make use of the %MVR function to change the collation to handle the strings as numeric values:

SELECT Name,Home_Street

FROM Sample.Person

ORDER BY %MVR(Home_Street)

 

NameHome_Street
Novello,Tara D.137 Franklin Place
Diavolo,Andrew V.194 Ash Drive
Ng,Roger R.279 Maple Place
Sands,Patrick Z.311 Clinton Drive
Kratzmann,Alfred C.376 Main Street
Jackson,John L.427 Elm Avenue
Lee,Wolfgang L.441 Oak Street
Dunlap,Kirsten J.455 Franklin Street

 

Although the %MVR function is explained in the Cache documentation, it can be difficult to find if you don't know what to search for,  so hopefully this might help other developers with the same requirement.

Comments

up to now I would do it:

SELECT Name,Home_Street FROM Sample.Person ORDER BY +Home_Street

Where's the improvement ? I see no difference.

There is a difference:

Name Home_Street
ORDER BY %MVR(Home_Street)
Harrison,Greta U. 9428 Madison Place
Leiberman,Emma L. 9428 Maple Blvd

Name Home_Street
ORDER BY +Home_Street
Leiberman,Emma L. 9428 Maple Blvd
Harrison,Greta U. 9428 Madison Place