Source Code for http://www.brettb.com/SQL_Help_IsNumeric_Function.asp
<html>
<head>
<title>SQL Server Help: Detecting Numbers in Transact-SQL Using the 'IsNumeric' Function</title>
<meta name="description"
content="This SQL Help article describes how to make use of SQL Server's Transact-SQL 'IsNumeric' function">
<meta name="keywords"
content="tutorial, example, sql server, tsql, transact sql, sql, isnumeric, string, strings, numerical, number, numbers, digit">
<link REL="stylesheet" HREF="BrettbDotCom.css" TYPE="text/css">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
</head>
<body>
<script language="JavaScript">
function SubmitHeaderSearchForm() {
document.FormSubmitHeaderSearch.submit();
}
</script>
<div align="center"><table border="0" cellpadding="0" cellspacing="0" width="800"
style="border: 1px solid rgb(0,0,0)">
<tr>
<td width="167"><a href="Default.asp" title="Brettb.Com (Back to Home Page)"><img src="Images/BrettbDotCom_Logo.jpg" width="167" height="72"
alt="Brettb.Com" border="0"></a></td>
<td width="633"><img src="HeaderImages/PurpleFlame.jpg" width="633" height="72" alt=""></td>
</tr>
</table><table border="0" cellpadding="0" width="802" bgcolor="#DD3333"
style="border-left: 1px solid rgb(0,0,0); border-right: 1px solid rgb(0,0,0); border-top: 1px none rgb(0,0,0); border-bottom: 1px solid rgb(0,0,0)">
<tr>
<td class="TDHeader" valign="middle" align="left"> <a href="Default.asp"
title="HOME">HOME</a> | <a href="web.asp" title="ABOUT ME">ABOUT ME</a> | <a
href="Biotechnology.asp" title="BIOTECHNOLOGY">BIOTECHNOLOGY</a> | <a
href="technicalwriting.asp" title="ARTICLES">ARTICLES</a> | <a
href="DeveloperTools.asp" title="DEVELOPER TOOLS">TOOLS</a> | <a href="Gallery.asp"
title="GALLERY">GALLERY</a> | <a href="contact.asp" title="CONTACT">CONTACT</a></td>
<form method="POST" action="SearchResults.asp" name="FormSubmitHeaderSearch"><td align="right" class="TDHeader" valign="middle">Search: <input type="text" name="query"
size="20" maxlength="100"> <a href="javascript:SubmitHeaderSearchForm();">Go</a></td></form>
</tr>
</table>
</center></div>
<div align="center"><center>
<table border="0" cellpadding="8" cellspacing="0" width="802"
style="border-left: 1px solid rgb(0,0,0); border-right: 1px solid rgb(0,0,0)">
<tr>
<td bgcolor="#E0E0E0" width="151" valign="top" align="left" class="TDLeftPanel" nowrap><strong><a href="DeveloperTools.asp" title="DEVELOPER TOOLS">DEVELOPER TOOLS</a><br>
</strong> <a href="ASPDocumentationTool.asp" title="ASP Documentation Tool">ASP Doc
Tool</a><br>
<a href="ASPNetDocumentationTool.asp" title="ASP.NET Documentation Tool">ASP.NET Doc
Tool</a><br>
<a href="http://www.winnershtriangle.com/w/Products.SQLDocumentationTool.asp" title="SQL Documentation Tool" target="_blank">SQL Doc
Tool</a><br>
<a href="IndexServerCompanion.asp" title="Index Server Companion">Index Server
Companion</a><br>
<a href="TheWebsiteUtility.asp" title="The Website Utility">The Website Utility</a><p><strong>TECHNICAL
ARTICLES <br>
</strong> <a href="ASPWatchArticles.asp" title="ASP Articles">ASP</a><br>
<a href="ASP.NETArticles.asp" title="ASP.NET Articles">ASP.NET</a><br>
<a href="JavaScriptArticles.asp" title="JavaScript Articles">JavaScript</a><br>
<a href="SQL_Help.asp" title="Transact SQL Articles">Transact SQL</a></p>
<p><strong>PHOTO GALLERIES</strong><br>
<a href="CanonEOS300D_Gallery1.asp" title="Canon EOS 300D Samples">Canon EOS 300D
Samples</a><br>
<a href="Red_Arrows_2004.asp" title="Red Arrows 2004">Red Arrows 2004</a><br>
<a href="Living_Coasts_Photos.asp" title="Living Coasts">Living Coasts</a><br>
<a href="backgrounds.asp" title="Web Page Backgrounds">Web Page Backgrounds</a><br>
<a href="gallery.asp" title="More Galleries...">More Galleries...</a></p>
<p><strong>NEW STUFF</strong><br>
<a href="TransactSQLColorCoder.asp" title="Transact SQL Color Coder">SQL Color Coder</a><br>
<a href="CanonEOS300D_Gallery3.asp" title="Canon EOS 300D Samples">Canon EOS 300D
Samples</a><br>
<a href="TheWebsiteUtility.asp" title="The Website Utility">The Website Utility</a><br>
<a href="Website_Search_Engine_Optimisation.asp" title="Useful Search Engine Optimization Techniques">Search Engine Optimisation</a><br>
<a href="BuildingAnASPSearchEngine.asp" title="Creating an ASP Search Engine for your Website">Build an ASP Search Engine</a><br>
<a href="MyTropicalFishtank.asp" title="My Tropical Freshwater Fishtank">My Tropical Fishtank</a><br>
<a href="Investments_ISAs.asp" title="Savings & Investments">Savings & Investments</a><br>
<a href="what's_new.asp" title="What's New?">Other New Stuff...</a></p>
<p><strong>POPULAR STUFF</strong><br>
<a href="VBScriptRegularExpressions.asp" title="VBScript Regular Expressions">Regular
Expressions</a><br>
<a href="ASPDocumentationTool.asp">ASP Documentation Tool</a><br>
<a href="SearchingIndexServerWithASP.asp" title="Index Server & ASP">Index
Server & ASP</a><br>
<a href="js_banner_ad_rotator.asp" title="JavaScript Banner Ad Rotator">JavaScript Ad Rotator</a></p>
<p><strong>LINKS</strong><br>
<a href="http://www.winnershtriangle.com/w/Default.asp" title="Business Website"
target="_blank">Business Website</a><br>
<a href="http://authors.aspalliance.com/brettb/" title="ASPAlliance Articles"
target="_blank">ASPAlliance Articles</a><br>
</p>
<p><img alt="Microsoft Certified Professional"
src="images/MCP_c_smaller.gif" width="151" height="38"></p>
<p align="center">
<script type="text/javascript"><!--
google_ad_client = "pub-7044749527879330";
google_alternate_color = "FFFFFF";
google_ad_width = 120;
google_ad_height = 90;
google_color_border = "000000";
google_color_bg = "DDDDDD";
google_color_link = "FF0000";
google_color_url = "DD3333";
google_color_text = "CCCCCC";
google_ad_format = "120x90_0ads_al_s";
google_ad_channel ="6714185213";
//--></script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</p>
<p align="center">
<SCRIPT type='text/javascript' language='JavaScript' src='http://xslt.alexa.com/site_stats/js/s/a?amzn_id=brettbcom&url=www.brettb.com'></SCRIPT>
</p>
</td>
<td width="551" valign="top" align="left">
<p><a href="Default.asp" title="Home">Home</a> > <a href="technicalwriting.asp"
title="Articles">Articles</a> > <a href="SQL_Help.asp"
title="Transact SQL Programming Articles">Transact SQL Programming Articles</a></p>
<h1>Detecting Numbers in Transact-SQL Using the 'IsNumeric' Function</h1>
<p>The Transact SQL <strong>IsNumeric</strong> function can be used to determine if a
value is numeric. It can be used on column values as well as other arbritrary items. The
IsNumeric function takes a single argument - the expression that needs to be determined
whether it is numeric or not. The function returns <strong>1</strong> for numeric values,
and <strong>0</strong> for non-numeric values.</p>
<p>The following simple example shows if the <em>PostCode</em> field of the <em>Suppliers</em>
table in the <em>Northwind</em> sample database is numeric:</p>
<p><font color="#000080">SELECT CompanyName, PostalCode,<br>
isnumeric(PostalCode) AS 'PostCodeIsNumeric'<br>
FROM suppliers</font></p>
<p>So in this example, the <em>PostCodeIsNumeric</em> column would contain a value of <em>0</em>
for the postcode <em>EC1 4SD</em>, but <em>1</em> for the postcode <em>70117</em>.</p>
<h2>Incorporating IsNumeric With Case ... When Statements</h2>
<p>Using the IsNumeric function with <a href="SQL_Help_Case_Statement.asp">Case ... When
SQL statements</a> can be useful. The following is a basic example:</p>
<p><font color="#000080">SELECT CompanyName, PostalCode,<br>
CASE WHEN isnumeric(PostalCode) = 1 THEN <br>
'Numeric'<br>
ELSE<br>
'Not Numeric'<br>
END<br>
PostCodeIsNumeric<br>
<br>
FROM suppliers</font></p>
<h2>Using IsNumeric to Numerically Sort DataView Columns</h2>
<p>In Microsoft's .NET Framework used in ASP.NET, C# and VB.NET it is possible to use the
Sort() method to sort datasets by a specific column. Unfortunately the Sort() method only
sorts alphabetically, so a column with a value of 100 will appear before a column with a
value of 1.</p>
<p>The solution to this is to output an extra column in the SQL result set that populates
the DataSet from which the DataView is derived. The trick is to create a new column that
prefixes zeros to numerical values, so that for example, a column with a value of 1 will
become 00001 and 100 will become 00100. This column can then be used to sort the existing
column.</p>
<p>Don't forget that this new column doesn't have to be displayed within the DataGrid, it
just needs to be returned with the SQL results set.</p>
<p>The SQL code example below uses this technique to allow a DataGrid to be produced
whereby the employee's telephone extension number is sortable. Since the extension numbers
can be either a three or a four digit number, this allows three digit numbers to be
returned before four digit numbers. Note that the number 6 in the code refers to the
number of times the zero is replicated in the (i.e. the length of the number to be
returned). If longer numbers occur in the column then this number will have to be
increased accordingly.</p>
<p><font color="#000080">SELECT EmployeeID, FirstName, LastName, Extension,<br>
<br>
CASE WHEN isnumeric(Extension) = 1 THEN <br>
replicate('0', 6 - len(Extension)) + Extension<br>
ELSE<br>
replicate('0', 6)<br>
END<br>
NumericExtension<br>
<br>
FROM Employees</font>
<ul>
<li><font SIZE="2"><a href="ASPNETNumericallySortDataView.asp">More information about using
this with DataViews</a>.</font></li>
</ul>
<font SIZE="2">
<h2>Useful Links</h2>
<ul>
<li>The <strong><a
href="http://www.winnershtriangle.com/w/Products.SQLDocumentationTool.asp" target="_blank" title="SQL Documentation Tool">SQL
Documentation Tool</a></strong> builds technical documentation for Microsoft SQL Server
databases. A free trial version is available for download.</li>
</ul>
<ul>
<li>The <a href="http://www.winnershtriangle.com/w/Products.ASPDocumentationTool.asp"
title="ASP Documentation Tool">ASP Documentation Tool</a> can automatically document your
ASP (VBScript and JScript) websites and SQL Server/Microsoft Access databases. An <a
href="http://www.winnershtriangle.com/w/Products.ASPNETDocumentationTool.asp">ASP.NET
Documentation Tool</a> is also available!</li>
</ul>
<p>
</td>
<td width="100" valign="top" align="left">
<script type="text/javascript"><!--
google_ad_client = "pub-7044749527879330";
google_ad_width = 120;
google_ad_height = 600;
google_ad_format = "120x600_as";
google_ad_channel ="9212633149";
google_ad_type = "text_image";
google_color_border = "CCCCCC";
google_color_bg = "FFFFFF";
google_color_link = "0000FF";
google_color_url = "DD3333";
google_color_text = "000000";
google_alternate_ad_url = "http://www.brettb.com/NoGoogleAds.asp";
//--></script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
<iframe marginwidth="0" marginheight="0" src="http://rcm.amazon.com/e/cm?t=brettbcom&o=1&p=10&l=st1&mode=books&search=sql server&=1&fc1=&lc1=<1=&bg1=&f=ifr" width="130" height="460" border="0" frameborder="0" style="border:none;" scrolling="no"></iframe>
</td>
</tr>
</table>
</center></div>
<div align="center"><center>
<table border="0" cellpadding="2" cellspacing="0" width="802"
style="border: 1px solid rgb(0,0,0)">
<tr>
<td class="TDFooter"> <a href="toc.asp" title="Site Map">Site Map</a></td>
<td class="TDFooter"><p align="right">All content is © 1995 - 2006 Brett Burridge</td>
</tr>
</table>
</center></div> </p>
</font>
</body>
</html>