<

[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x

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

Published on
31,569 Points
18,969 Views
6 Endorsements
Last Modified:
Awarded
Kevin Cross
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
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:

The Data.
What's wrong with the natural sort order?
Custom Sorting in MS SQL Server: IsNumeric() Primer.
MySQL IsNumeric() Workaround.
MySQL IsNumeric() User Defined Function (UDF).
Custom Sorting in MySQL: Using IsNumeric() UDF.

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

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

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
;

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

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

Open in new window

3	AUT	A-1014
14	ISL	A-987

Open in new window

6	CHN	215021
5	CHL	282-1255

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

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

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
;

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

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
;

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
;

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

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
;

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

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

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

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

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

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 ;

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)

Open in new window

MySQL without UDF:
postalcode regexp '^[0-9]+$'

Open in new window


Convert integer string to actual proper data type.
T-SQL:
cast(postalcode as int)

Open in new window

MySQL:
cast(postalcode as signed)

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
   ) 

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
   ) 

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

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

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

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 ;

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
;

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
;

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
6
Comment
Author:Kevin Cross
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 60

Author Comment

by:Kevin Cross
As I did for Oracle, whenever I find syntax that is comparable I like to share if nothing else than for those who stumble upon this with other database systems, but guess I like being re-enforced to just how similarly SQL syntax is across platforms.  Love standardization.

Anyway, stumbled onto a PostgreSQL question today that I answered using Pattern Matching and figured I would share syntax for testing IsNumeric().  

Enjoy!


References:
http://www.postgresql.org/docs/8.4/static/functions-conditional.html#AEN15198
http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-TABLE

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

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
In Oracle, the prevaling method is to cast the string as a number and catch any exception.
Example:
http://www.oracle.com/technology/oramag/oracle/04-jul/o44asktom.html

0
 
LVL 60

Author Comment

by:Kevin Cross
Thanks, Aikimark.  Although, I was simply showing a comparable syntax here with regexp_like(), it is better to see syntax that is more widely used by those in the trenches of Oracle.  

Does that perform better due to not incurring the overhead of regular expressions?

Or simply when generating function, regexp_like() isn't in context?  

Thanks again.

Regards,

Kevin
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Expert Comment

by:aikimark
@mwvisa1

I don't think performance is a factor in this choice.  The recommended Oracle method is more general than the RegExp method, since it recognizes float and scientific notation as well as integers.
0
 
LVL 60

Author Comment

by:Kevin Cross
That makes sense.  Same principal in most non-database programming languages, you try to cast and then use try/catch.  Whereas to get the same functionality the regular expression would get pretty ugly.  Thanks for the link again!
0
 
LVL 38

Expert Comment

by:younghv
An amazing amount of work to support other Members of EE - thank you.
Big ol' yes vote!
0
 
LVL 27

Expert Comment

by:skullnobrains
only answering to the article's subject

you can also use cast in mysql and many other database engines instead of relying on regexpr

just cast and compare the casted value to the original one will produce a reverse cast so the values will differ if the cast produced data loss which would happen if you cast a postal code with letters as an integer

select '01264abcd' == cast ( '01264abcd' as unsigned)
0
 
LVL 60

Author Comment

by:Kevin Cross
Yes, VAL() in access does the same, but on some platforms CAST will fail because the string is not numeric; hence, the need for ISNUMERIC () check before casting.
0
 
LVL 15

Expert Comment

by:dbbishop
Something you need to be aware of, in SQL Server, the value '1234E32' will pass the ISNUMERIC() check (returns 1), but will fail the CAST() if casting to any data type other than FLOAT. Any string of numbers with either a single 'E' or 'D' in the string will be interpreted as scientific notation.
0
 
LVL 27

Expert Comment

by:skullnobrains
likewise in mysql

select 3e2 REGEXP '^300$';
+--------------------+
| 3e2 REGEXP '^300$' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

... but if you quote the '3e2', it won't happen.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month