Solved

Insert 0 instead of Varchar

Posted on 2007-11-13
4
283 Views
Last Modified: 2012-05-11
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
Comment
Question by:JRockFL
4 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
ID: 20277563
Hi,

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

HTH
  David
0
 
LVL 14

Assisted Solution

by:cezarF
cezarF earned 250 total points
ID: 20277570
SELECT CASE ISNUMERIC(fieldname1) WHEN 1 THEN fieldname1 ELSE 0 END AS fieldname1 FROM table1
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20277702
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
 
LVL 8

Author Comment

by:JRockFL
ID: 20277713
Thank you! I will give that a try
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question