CAST and IsNumeric

by

IsNumeric returns 1 if the varchar value can be converted to ANY number type. This includes int, bigint, decimal, numeric, real & float.

Scientific notation could be causing you a problem. For example:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values(NULL)
Insert Into @Temp Values('1')
Insert Into @Temp Values('1e4')
Insert Into @Temp Values('Not a number')

Select Cast(Data as bigint)
From   @Temp
Where  IsNumeric(Data) = 1 And Data Is Not NULL

There is a trick you can use with IsNumeric so that it returns 0 for numbers with scientific notation. You can apply a similar trick to prevent decimal values.

IsNumeric(YourColumn + ‘e0’)

IsNumeric(YourColumn + ‘.0e0’)

Try it out.

SELECT CAST(myVarcharColumn AS bigint)
FROM myTable
WHERE IsNumeric(myVarcharColumn + '.0e0') = 1 AND myVarcharColumn IS NOT NULL
GROUP BY myVarcharColumn

More Related Contents:

  1. T-SQL: Opposite to string concatenation – how to split string into multiple records [duplicate]
  2. What is the best way to create and populate a numbers table?
  3. Sql Server string to date conversion
  4. SQL Server query to find all permissions/access for all users in a database
  5. SQL Server IN vs. EXISTS Performance
  6. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  7. PIVOT in sql 2005
  8. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  9. Do I really need to use “SET XACT_ABORT ON”?
  10. Creating audit triggers in SQL Server
  11. Regular Expressions in SQL Server servers?
  12. How do you specify a different port number in SQL Management Studio?
  13. How to determine total number of open/active connections in ms sql server 2005
  14. How to remove accents and all chars a..z in sql-server?
  15. Find non-ASCII characters in varchar columns using SQL Server
  16. How to query values from xml nodes?
  17. Diagnosing Deadlocks in SQL Server 2005
  18. How to query for Xml values and attributes from table in SQL Server?
  19. Disadvantages of MARS (Multiple Active Result Sets)?
  20. Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005
  21. Drop primary key using script in SQL Server database [duplicate]
  22. sql use statement with variable
  23. SQL Server recursive query
  24. Milliseconds wrong when converting from XML to SQL Server datetime
  25. nvarchar(max) vs NText
  26. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  27. Date range overlapping check constraint
  28. Difference between database and schema
  29. What are row, page and table locks? And when they are acquired?
  30. Forgot SQL Server Password

Leave a Comment