Insert 0 instead of Varchar

I am inserting data from one table to another.
The column in table 1 is varchar
The column in table 2 is int

If the data in table is not a number, for example "E"
how can I insert 0 instead?

Most of the data is numbers, but I have come across some letters and would just like to insert zero.
LVL 8
JRockFLAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

select
  case
    when isnumeric( table1.col ) = 1 then cast( table1.col as int )
    else 0
  end
from dbo.table1

HTH
  David
0
 
cezarFConnect With a Mentor Commented:
SELECT CASE ISNUMERIC(fieldname1) WHEN 1 THEN fieldname1 ELSE 0 END AS fieldname1 FROM table1
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
ISNUMERIC() is not a trustable one, for example ISNUMERC('$') will return 1.

refer this
http://sequelserver.blogspot.com/2007/10/selecting-numeric-values-from-column.html

aneesh
0
 
JRockFLAuthor Commented:
Thank you! I will give that a try
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.