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

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`)
)
;
                                  

1:
2:
3:
4:
5:
6:
7:

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'
;
                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:

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
;
                                  

1:
2:
3:
4:

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
                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

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
                                  

1:
2:

3	AUT	A-1014
14	ISL	A-987
                                  

1:
2:

6	CHN	215021
5	CHL	282-1255
                                  

1:
2:

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
                                  

1:
2:

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
                                  

1:
2:

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.

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
;
                                  

1:
2:
3:
4:

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
                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

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
;
                                  

1:
2:
3:
4:
5:
6:
7:
8:

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
;
                                  

1:
2:
3:
4:
5:
6:
7:
8:

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
                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

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
;
                                  

1:
2:
3:
4:
5:
6:
7:
8:

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
                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

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

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]+))))$
                                  

1:

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})
                                  

1:

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]+)))
                                  

1:

*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]+$
                                  

1:

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.

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 ;
                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

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.

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)
                                  

1:

MySQL without UDF:

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

1:

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

cast(postalcode as int)
                                  

1:

MySQL:

cast(postalcode as signed)
                                  

1:

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

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

1:
2:
3:
4:

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
   ) 
                                  

1:
2:
3:
4:

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 */
;
                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

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
                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

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.