Select records that fail casting varchar column to bigint

is there an easy way to get all records where casting a varchar column to an bigint would fail? This causes a conversion error:

SELECT CAST(IMEI AS BIGINT)FROM RMA

Use this sql as example:

if OBJECT_ID('tempdb..#RMA') is not null
    DROP TABLE #RMA

CREATE TABLE #RMA
( 
    IMEI VARCHAR(20)
)
INSERT INTO  #RMA(IMEI)VALUES('352382021485772')
INSERT INTO  #RMA(IMEI)VALUES('352022033456409')
INSERT INTO  #RMA(IMEI)VALUES('BN332VWY653577440220')

SELECT * FROM  #RMA
SELECT CAST(IMEI AS BIGINT)FROM #RMA

DROP TABLE #RMA

So, in this example i need only the record with IMEI='BN332VWY653577440220'.

Thank you.

Answers


Try the T-SQL ISNUMERIC function:

SELECT IMEI 
FROM  #RMA
WHERE ISNUMERIC(IMEI) = 0   -- not numeric

See the MSDN SQL Server Books Online docs for it.


Need Your Help

Calendar doesn't displayed using sfJQueryUIPlugin

jquery forms symfony1 doctrine symfony-1.4

I have installed the sfJQueryUIPlugin for my project, which I want to display a date picker for a birthday selection. therefore after installation I've change my form widgets to

How to implement recaptcha in MVC 4 project?

c#-4.0 asp.net-mvc-4 recaptcha

I have searched the Internet and a few threads showed up in stackoverlow about implementing recaptcha in mvc by following this tutorial. However, I found the step by step instruction very lacking I...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.