SQL Sorting of strings containing a numeric component - %MVR

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

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)

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.