TECHNICAL
ARTICLES ASP ASP.NET JavaScript Transact SQL Other Articles Software Reviews PHOTO GALLERIES TRAVEL LOG MORE STUFF POPULAR STUFF 
Home > Articles > Transact SQL Programming Articles Detecting Numbers in TransactSQL Using the 'IsNumeric' FunctionThe 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 nonnumeric values. The following simple example shows if the PostCode field of the Suppliers table in the Northwind sample database is numeric: SELECT CompanyName, PostalCode, 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 StatementsUsing the IsNumeric function with Case ... When SQL statements can be useful. The following is a basic example: SELECT CompanyName, PostalCode, Using IsNumeric to Numerically Sort DataView ColumnsIn 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, Useful Links

Site Map  Privacy Policy  All content is © 1995  2012 Brett B 