isNumeric function in MS T-SQL

By David Fekke
January 17th, 2011

I do a lot of data conversions with my current job. I use a lot string and date function that are provided in Microsoft SQL Server 2000 in order to validate column when doing my bulk inserts and updates. I came across one today that is in the books online, but I did not realize it was there.

I use ColdFusion functions all the time like isDate and isNumeric to check variables for proper type. Here is an example of where these functions can be used in bulk inserts.

USE myFakeDB; GO

INSERT INTO myTable (oldCustomerID)
SELECT CASE isNumeric(CustomerNumber)
WHEN 1 THEN CustomerNumber
FROM myTempTable
ON myTempTable.CustomerNumber = myTable.oldCustomerID
WHERE myTable.myPrimaryKey IS NULL;

This will also work on bulk updates as well.

← Previous Page  Next Page →