Solved

Insert 0 instead of Varchar

Posted on 2007-11-13
4
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 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