Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

Detecting Numbers in Transact-SQL Using the 'IsNumeric' Function

The Transact SQL IsNumeric 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 1 for numeric values, and 0 for non-numeric values.

The following simple example shows if the PostCode field of the Suppliers table in the Northwind sample database is numeric:

SELECT CompanyName, PostalCode,
isnumeric(PostalCode) AS 'PostCodeIsNumeric'
FROM suppliers

So in this example, the PostCodeIsNumeric column would contain a value of 0 for the postcode EC1 4SD, but 1 for the postcode 70117.

Incorporating IsNumeric With Case ... When Statements

Using the IsNumeric function with Case ... When SQL statements can be useful. The following is a basic example:

SELECT CompanyName, PostalCode,
    CASE WHEN isnumeric(PostalCode) = 1 THEN
        'Numeric'
    ELSE
        'Not Numeric'
    END
        PostCodeIsNumeric

FROM suppliers

Using IsNumeric to Numerically Sort DataView Columns

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.

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.

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.

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.

SELECT EmployeeID, FirstName, LastName, Extension,

CASE WHEN isnumeric(Extension) = 1 THEN
    replicate('0', 6 - len(Extension)) + Extension
ELSE
    replicate('0', 6)
END
    NumericExtension

FROM Employees

Useful Links

  • The SQL Documentation Tool builds technical documentation for Microsoft SQL Server databases. A free trial version is available for download.

  Site Map | Privacy Policy

All content is 1995 - 2012