How to Mimic the T-SQL IsNumeric() Function in MySQL

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE.

Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as numbers instead of character string which causes '9' to sort after '10').  I won't get into the debate on whether this is an appropriate way to store data in this article, but will presume we can't change it and so must deal with how to sort correctly.  

With that in mind, the following article was written to show how important an IsNumeric() function can be in solving this problem and further how to specifically to do this in MySQL server.

Since this can be useful to MySQL users of all experience levels, it goes through details for beginners; therefore, for intermediate/advanced users who simply want to cut to the solution, I have organized the article into the following sections to make it easy to jump around:


Before we get started.
Please note that, although experience can be varied, all will need to have MySQL Server, a tool such as MySQL Query Browser, and a basic knowledge of how to execute SQL statements as pre-requisites.

Download MySQL Community Server and GUI Tools

Optionally, for those wanting to play with T-SQL examples as well, then MS SQL Server and a tool such as SQL Server Management Studio (SSMS) are also pre-requisites.  More experience on creating and executing DDL and other statements for T-SQL are also required as some code is only shown in MySQL syntax.

Download MS SQL Server Express and Management Tools

0. The Data.

Here is an example data scenario:
+alphanumeric column
+optionally contains a hyphen
+sorting needs to order text and numbers separately
+if a hyphen does exist, sort needs to treat text and numbers on either side of hyphen separately

From this point forward, I will reference the above data scenario as table country_postalcodes.  

Therefore, to play along you can use the following steps in MySQL to setup the sample data being referenced:

1. Create a simple table.
A column for string data and a primary key will do, but let's try a table of postal codes by country to give a more real life example of the kind of unstructured data we may have to live with.

create table `country_postalcodes` (
                        `id` integer unsigned not null auto_increment,
                        `country` varchar(3) not null,
                        `postalcode` varchar(10) not null,
                         primary key(`id`)
                      )
                      ;

Select all Open in new window

2. Insert sample data into table from previous step.

insert into country_postalcodes(country, postalcode)
                      select 'OMN', 'PC-133'
                      union select 'DEU', '45128'
                      union select 'POL', '80-958'
                      union select 'USA', '78759'
                      union select 'FIN', '00950'
                      union select 'GTM', '01057'
                      union select 'SWE', 'AB85176'
                      union select 'CHL', '282-1255'
                      union select 'NLD', '3980CD'
                      union select 'CHN', '215021'
                      union select 'USA', '33166'
                      union select 'ISR', '66851'
                      union select 'ARG', '1674'
                      union select 'KWT', '40180'
                      union select 'ISL', 'A-987'
                      union select 'VEN', '1040A'
                      union select 'GER', '60549'
                      union select 'CZE', '10200'
                      union select 'CHE', 'CH8127'
                      union select 'ITA', '04011'
                      union select 'SGP', '338763'
                      union select 'MEX', 'CP76020'
                      union select 'IDN', '76116'
                      union select 'USA', '77043'
                      union select 'GRC', '12243'
                      union select 'USA', '49417'
                      union select 'ASM', '96799'
                      union select 'TUR', '38030'
                      union select 'OMN', 'PC-A12'
                      union select 'AUT', 'A-1014'
                      ;

Select all Open in new window

3. Verify.
To test your data inserted ok and see the default textual sort, execute the following SQL statement:

select id, country, postalcode
                      from country_postalcodes
                      order by postalcode
                      ;

Select all Open in new window

Results should appear similar to the below, barring any differences in auto-increment id:

id	country	postalcode
                      9	FIN	00950
                      12	GTM	01057
                      16	ITA	04011
                      7	CZE	10200
                      30	VEN	1040A
                      11	GRC	12243
                      1	ARG	1674
                      6	CHN	215021
                      5	CHL	282-1255
                      26	USA	33166
                      23	SGP	338763
                      25	TUR	38030
                      19	NLD	3980CD
                      17	KWT	40180
                      8	DEU	45128
                      27	USA	49417
                      10	GER	60549
                      15	ISR	66851
                      13	IDN	76116
                      28	USA	77043
                      29	USA	78759
                      22	POL	80-958
                      2	ASM	96799
                      3	AUT	A-1014
                      14	ISL	A-987
                      24	SWE	AB85176
                      4	CHE	CH8127
                      18	MEX	CP76020
                      20	OMN	PC-133
                      21	OMN	PC-A12

Select all Open in new window

Now that our data is setup and our baseline (natural) sorting is established as seen in our data section, we can start to examine things further.  

1. What's wrong with the natural sort order?

Since the natural sort order above is correct for alphanumerically sorting a text column, let's take a look at what is wrong.

1. Numerical ordering is incorrect:

11	GRC	12243
                      1	ARG	1674

Select all Open in new window

3	AUT	A-1014
                      14	ISL	A-987

Select all Open in new window

6	CHN	215021
                      5	CHL	282-1255

Select all Open in new window

Second example also plants the seed for our more complex need of sorting on either side of a separator like hyphen ("-").  The third is another example for our complex case as values with numeric only data on both sides of "-" will need to be treated/sorted as numeric so that for example United States zip+4 style postal code 45458-1234 would be sorted with shortened code 45458 as they would as text but additionally in the correct numerical sequence with the remainder of the data.

2. Numbers and text are sorted together:

7	CZE	10200
                      30	VEN	1040A

Select all Open in new window

Aside from wanting these separated, note how the grouping of alphanumeric and numeric only data causes seemingly incorrect numerical ordering.  As another learning experience, you can probably explore sorting this text (beginning with numbers) to correctly align with numerical values just with a suffix; however, we will continue to keep heading down our original path where these values should be considered as text.

3. Numbers are always first:

20	OMN	PC-133
                      21	OMN	PC-A12

Select all Open in new window

Not really a problem with the fact that numbers are first in an alphanumeric sort, but drawing to your attention here as we will explore changing this behavior to show some added benefit of our methodology.

2. Custom Sorting in MS SQL Server: IsNumeric() Primer.

Note:
As the section heading implies, the following is T-SQL syntax, so please be aware that code is not promised to work on other platforms but may if they have an IsNumeric() function natively.

There are other ways to approach this, but our goal here is to show how we can custom sort our data if we were using MS SQL Server for example which has an IsNumeric() function.  IsNumeric() can be used to test a string to see if it solely contains data that is valid for a number of numeric types.  

For our example, we are going to strictly deal with its ability to recognize integers.  Therefore, let's look at how simple the IsNumeric() function corrects the latter two issues illustrated above in our analysis of the alphanumeric sort against our requirements.

select id, country, postalcode
                      from country_postalcodes
                      order by isnumeric(postalcode), postalcode
                      ;

Select all Open in new window

And with the results, you will see that the new sort now has all text based values at the top of the list ordered alphabetically; then, numeric ones.

id	country	postalcode
                      30	VEN	1040A
                      5	CHL	282-1255
                      19	NLD	3980CD
                      22	POL	80-958
                      3	AUT	A-1014
                      14	ISL	A-987
                      24	SWE	AB85176
                      4	CHE	CH8127
                      18	MEX	CP76020
                      20	OMN	PC-133
                      21	OMN	PC-A12
                      9	FIN	00950
                      12	GTM	01057
                      16	ITA	04011
                      7	CZE	10200
                      11	GRC	12243
                      1	ARG	1674
                      6	CHN	215021
                      26	USA	33166
                      23	SGP	338763
                      25	TUR	38030
                      17	KWT	40180
                      8	DEU	45128
                      27	USA	49417
                      10	GER	60549
                      15	ISR	66851
                      13	IDN	76116
                      28	USA	77043
                      29	USA	78759
                      2	ASM	96799

Select all Open in new window

So without a lot of effort, IsNumeric() has just eliminated two-thirds of our task or at least fifty percent of our worries since our last task has two parts: numerical sequencing; handling left/right side of "-" based on three other rules.

For the first of the two, we modify our code to:

select id, country, postalcode
                      from country_postalcodes
                      order by 
                         case isnumeric(postalcode)
                            when 0 then 0
                            else cast(postalcode as int)
                         end, postalcode
                      ;

Select all Open in new window

or

select id, country, postalcode
                      from country_postalcodes
                      order by isnumeric(postalcode),
                         case isnumeric(postalcode)
                            when 0 then postalcode
                            else right(replicate('0',10)+postalcode, 10)
                         end
                      ;

Select all Open in new window

Resulting in (top 15 shown):

id	country	postalcode
                      30	VEN	1040A
                      5	CHL	282-1255
                      19	NLD	3980CD
                      22	POL	80-958
                      3	AUT	A-1014
                      14	ISL	A-987
                      24	SWE	AB85176
                      4	CHE	CH8127
                      18	MEX	CP76020
                      20	OMN	PC-133
                      21	OMN	PC-A12
                      9	FIN	00950
                      12	GTM	01057
                      1	ARG	1674
                      16	ITA	04011

Select all Open in new window

Wonderful!

As you can see correctly sorts 1674 between 01057 and 04011 now.  

The two different code samples were to illustrate that you can use the IsNumeric() as a way to determine which of two logics to use, but we don't necessarily need to cast/convert our data type; however, I will carry the former through the remainder of the article since it does the following: doesn't repeat usage of IsNumeric(); gives us the ability to sort the text and numbers differently with respect to asc(ending) or desc(ending) order.

Because the (switch) case determines how the numbers will sort in relation to the text, while the secondary order by column sorts the remaining values alphabetically, although as shown both text and numbers are using asc implicitly, we can very simply change to desc for numbers.

select id, country, postalcode
                      from country_postalcodes
                      order by 
                         case isnumeric(postalcode)
                            when 0 then 9999999999
                            else cast(postalcode as int)
                         end desc, postalcode
                      ;

Select all Open in new window

Resulting in (top 15 shown):

id	country	postalcode
                      30	VEN	1040A
                      5	CHL	282-1255
                      19	NLD	3980CD
                      22	POL	80-958
                      3	AUT	A-1014
                      14	ISL	A-987
                      24	SWE	AB85176
                      4	CHE	CH8127
                      18	MEX	CP76020
                      20	OMN	PC-133
                      21	OMN	PC-A12
                      23	SGP	338763
                      6	CHN	215021
                      2	ASM	96799
                      29	USA	78759

Select all Open in new window

Notice that the first text values are unchanged, but with our numbers are listed highest to lowest.

Now that we can appreciate the IsNumeric() function's value within the solution of our custom sorting task, let's step back and get to why we are here: how to duplicate this functionality in MySQL.  After which, we can get back to going for the 'gold' of handling our final portion of the sorting.

3. MySQL IsNumeric() Workaround.

The short answer, use the RegExp operator to test column or string literal against the following regular expression (or one comparable):

^(([0-9+-.$]{1})|([+-]?[$]?[0-9]*(([.]{1}[0-9]*)|([.]?[0-9]+))))$

Select all Open in new window

From my testing, this got me what I needed to mimic the majority if not all of the IsNumeric() functionality in MySQL other than what I mention in "How it works." segment.  

How it works.
The "^" symbol indicates start of value; the "$", the end.  Therefore, we will not get any false matches that merely contain numbers.  Here is another area which can be experimented with to accomplish task in scenarios where you may want to match mixed values.

Moving along, this is the first matching group which allows single-character strings to pass our test by matching on any of these characters: 0-9; "+" (positive sign); "-" (negative sign); "." (period); "$" (dollar sign - other currency symbols can be included also).

([0-9+-.$]{1})

Select all Open in new window

The "{1}" denotes that there is one and only one character present.  For minimum of one character and unlimited or other fixed maximum, the syntax changes to "{1, }" or "{1,n}", respectively.

The "|" symbol indicates an OR to specify that either the first group is matched or the second which is used for multi-character strings to ensure that symbols are ordered correctly (e.g., -$6.00, but -$6.0.0 is not nor is 6.00-$).

([+-]?[$]?[0-9]*(([.]{1}[0-9]*)|([.]?[0-9]+)))

Select all Open in new window

*Note: one known functionality not replicated, -$6.00 and $-6.00 should work, but didn't get into that for my version to obey the KISS ("keep it simple, silly") principle.

Some additional explanation on the last portion of the above regular expression syntax: "?" outside of [] indicates a character appears once or not at all; "*", zero or more times; "+", one or more times.  Using those definitions, you should be able to make out how the groupings are ensuring that we only have one "." for example; however, please feel free to post below for clarity.

Additionally, please leave me feedback if you find a case that doesn't work.

IsInteger()
Consequently, for our case here if using RegExp directly in code, I would use this simpler regular expression for integers:

^[0-9]+$

Select all Open in new window

The IsInteger() style implementation of IsNumeric() will lessen the amount of code needed, again to  follow the KISS principle since this shortened expression is all that is needed to ensure that our value begins, includes, and ends with only the digits 0-9 so that we match on numbers only and not alphanumeric strings.

So that's all we need, right?

Sure!  However, I didn't put you through all that reading for just a simple regular expression string, so...

4. MySQL IsNumeric() User Defined Function (UDF).

We just learned the workaround, so to make life easier, we can simply create a UDF called IsNumeric that we can call from our SQL queries in MySQL as we would from T-SQL on MS SQL platform.  I will not get into too much detail here, so please use the reference link below to learn more about creating functions in MySQL 5.x or higher.  This was different for MySQL 4.x and below as you had to create UDFs using C or C++ if I am not mistaken, but you can research for yourself if using one of those versions.

IsNumeric() UDF Code.

delimiter $$
                      
                      drop function if exists `isnumeric` $$
                      create function `isnumeric` (s varchar(255)) returns int
                      begin
                      set @match =
                         '^(([0-9+-.$]{1})|([+-]?[$]?[0-9]*(([.]{1}[0-9]*)|([.]?[0-9]+))))$';
                      
                      return if(s regexp @match, 1, 0);
                      end $$
                      
                      delimiter ;

Select all Open in new window

Voilà !

Our technique has been adapted into a neat IsNumeric() function that will take in a string, compare it with regexp, and then return a 1 or 0 based on the match like the T-SQL version.  For an IsInteger() implementation, see Appendix B.

And finally...

5. Custom Sorting in MySQL: Using IsNumeric() UDF.

Let's apply our IsNumeric() functionality in actual MySQL syntax while completing our complex sort order task.

Firstly, though, we will recap the key syntax we used for T-SQL and translate to MySQL.

Testing if a string represents an integer value.
T-SQL (or with MySQL UDF):

isnumeric(postalcode)

Select all Open in new window

MySQL without UDF:

postalcode regexp '^[0-9]+$'

Select all Open in new window

Convert integer string to actual proper data type.
T-SQL:

cast(postalcode as int)

Select all Open in new window

MySQL:

cast(postalcode as signed)

Select all Open in new window

Additional code (our final task).
Trick to get left portion before hyphen:

if(instr(postalcode, '-') > 0,
                            left(postalcode, instr(postalcode, '-')-1),
                            postalcode
                         ) 

Select all Open in new window

This portion of code uses Instr() to find the index of the hyphen ("-") within our value and then takes the left side using the Left() function.  We can do this directly before comparing to our regular expression in the order by case statement, although, is not shown as such below to keep the comparison to the T-SQL version very clean and clear.  Since there may not be a hyphen, the Instr() test is wrapped with the If() control function that ensures the index of hyphen is greater than 0 (it exists) before taking the left portion, otherwise use our entire value.

And very similarly, we obtain the right side of hyphen, defaulting to null when none exists:

if(instr(postalcode, '-') > 0,
                            right(postalcode, instr(reverse(postalcode), '-')-1),
                            null
                         ) 

Select all Open in new window

The use of Reverse() is the main difference in the Right() function than we did in the Left().  The reasoning here is if there is more than one hyphen, we will parse out the right most portion of the string.  This way you can support multiple hyphens without having to support code for each level -- just left most and right most values; everything in the middle will sort as text.  Again, keep it simple.

For more information on the string and control functions, please refer to the MySQL manuals online as to not detain us any further from the main point of showing our regular expression in action.

The Final Code.

select id, country, postalcode
                      from (
                         select *,
                       
                         /* split off left side */
                         if(instr(postalcode, '-') > 0,
                            left(postalcode, instr(postalcode, '-')-1),
                            postalcode
                         ) as postalcode_left,
                       
                         /* split off right side */
                         if(instr(postalcode, '-') > 0,
                            right(postalcode, instr(reverse(postalcode), '-')-1),
                            null
                         ) as postalcode_right
                         from country_postalcodes
                      ) derived
                      order by
                      
                      /* handles ordering of text to left of hyphen */
                         case isnumeric(postalcode_left)
                            when 0 then 9999999999
                            else cast(postalcode_left as signed)
                         end desc, postalcode_left /* sort non-numeric left text */,
                      
                      /* handles ordering of text to right of hyphen */
                         case isnumeric(postalcode_right)
                            when 0 then 9999999999
                            else cast(postalcode_right as signed)
                         end desc, postalcode_right /* sort non-numeric right text - catchall */
                      ;

Select all Open in new window

Resulting in our desired sort:

id	country	postalcode
                      16	VEN	1040A
                      9	NLD	3980CD
                      30	AUT	A-1014
                      15	ISL	A-987
                      7	SWE	AB85176
                      19	CHE	CH8127
                      22	MEX	CP76020
                      29	OMN	PC-A12
                      1	OMN	PC-133
                      21	SGP	338763
                      10	CHN	215021
                      27	ASM	96799
                      4	USA	78759
                      24	USA	77043
                      23	IDN	76116
                      12	ISR	66851
                      17	GER	60549
                      26	USA	49417
                      2	DEU	45128
                      14	KWT	40180
                      28	TUR	38030
                      11	USA	33166
                      25	GRC	12243
                      18	CZE	10200
                      20	ITA	04011
                      13	ARG	1674
                      6	GTM	01057
                      5	FIN	00950
                      8	CHL	282-1255
                      3	POL	80-958

Select all Open in new window

The code and results should speak for themselves; however, as stated before, please post comments below for any further clarification.  For code example using regular expression directly (without UDF creation), please see Appendix A.

Play around with our new code and you will see we have the added benefit of now sorting the text on the right and left sides differently as well.  Currently both are set to text then numbers desc, but we could easily make text desc on one side or numbers asc, etc.  Have fun.

Disclaimers:
The above code was developed and tested using MySQL 5.0.51 on Microsoft Windows Vista Business, but should work "as-is" on previous versions; however, you may need to consult the manual(s) online based on your installation.  Consequently, It is important to note that although our code will work "as-is" that it should not be assumed sufficient for every scenario, but hopefully it helps get you started or at least inspires you on your own custom sorting endeavor.

We have now learned how the IsNumeric() function works and how to emulate this functionality within the limits of MySQL's syntax.  Moreover, we made our own refined IsInteger() style function because of the flexibility gained by being able to edit the matching expression, all while learning some custom sorting when we have unstructured data that just won't go away.  And lastly, we have hopefully gained a nice base for using regular expressions in MySQL as that in itself as seen in our case is very useful.

Well, you made it this far, so congratulations!  If you feel like you have learned something new or at least are still smiling, then I accomplished my goal and would only ask in return that you vote this as helpful via the appropriate button at the top of the article.

Thanks for taking this journey with me, and happy coding!

Best regards,


Kevin (aka MWVisa1)


Appendix A: Custom Sorting in MySQL: Using Regular Expression.
If you didn't implement an IsNumeric() function, here is what our final code would look like:

select id, country, postalcode
                      from (
                         select *,
                      
                         /* split off left side */
                         if(instr(postalcode, '-') > 0,
                            left(postalcode, instr(postalcode, '-')-1),
                            postalcode
                         ) as postalcode_left,
                      
                         /* split off right side */
                         if(instr(postalcode, '-') > 0,
                            right(postalcode, instr(reverse(postalcode), '-')-1),
                            null
                         ) as postalcode_right
                         from country_postalcodes
                      ) derived
                      order by
                      
                      /* handles ordering of text to left of hyphen */
                         case postalcode_left regexp '^[0-9]+$'
                            when 0 then 9999999999
                            else cast(postalcode_left as signed)
                         end desc, postalcode_left /* sort non-numeric left text */,
                      
                      /* handles ordering of text to right of hyphen */
                         case postalcode_right regexp '^[0-9]+$'
                            when 0 then 9999999999
                            else cast(postalcode_right as signed)
                         end desc, postalcode_right /* sort non-numeric right text - catchall */
                      ;

Select all Open in new window

*Very simple update, but included here for convenience of copy and paste.


Appendix B: IsInteger() User Defined Function.
Using our simpler regular expression, we can add another utility function to our MySQL database:

delimiter $$
                      
                      drop function if exists `isinteger` $$
                      create function `isinteger` (s varchar(255)) returns int
                      begin
                      return if(s regexp '^[0-9]+$', 1, 0);
                      end $$
                      
                      delimiter ;

Select all Open in new window

Appendix C: For Oracle Users.
Rookie disclaimer:
I am still a novice Oracle SQL developer; therefore, this is as stated and bonus -- those with knowledge of better syntax please offer, but please be nice.

Since so many SQL platforms are similar, I did a little research just to see if Oracle had an IsNumeric() function, but from what I could gather 10g and lower does not.  Not sure if newer versions will, but aside from tricks I have seen using Translate() function I put together some code that demonstrates our RegExp() MySQL converted to Oracle in its simplest version.

Oracle 9i or higher:

select id, country, postalcode
                      from country_postalcodes
                      order by
                         case regexp_like(postalcode, '^[0-9]+$')
                            when 1 then cast(postalcode as integer)
                            else 9999999999
                         end, postalcode
                      ;

Select all Open in new window

Oracle below 9i:

select id, country, postalcode
                      from country_postalcodes
                      order by
                         if regexp_like(postalcode, '^[0-9]+$')
                            then cast(postalcode as integer)
                            else 9999999999
                         end if, postalcode
                      ;

Select all Open in new window

Check Oracle Tutorials for additional techniques and help with create function syntax specific to your version if capability exists.


References:
MS SQL IsNumeric() Function > http://msdn.microsoft.com/en-us/library/ms186272.aspx
MySQL Control Flow Functions > http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
MySQL Regular Expressions > http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp
MySQL String Functions > http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
MySQL Create Function > http://dev.mysql.com/doc/refman/5.0/en/adding-udf.html