Select records that cannot distinguish varchar column to bigint

Question

Is there an easy way to get all the records where dropping the varchar column in bigint fails? This causes a conversion error:

SELECT CAST(IMEI AS BIGINT)FROM RMA 

Use this sql as an 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 only need an entry with IMEI = 'BN332VWY653577440220'.

Thanks.

Answer 1

Try the T-SQL ISNUMERIC :

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

See the MSDN Books Online documents for it.