[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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.
0
JRockFL
Asked:
JRockFL
2 Solutions
 
David ToddSenior DBACommented:
Hi,

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

HTH
  David
0
 
cezarFCommented:
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now