?
Solved

Insert 0 instead of Varchar

Posted on 2007-11-13
4
Medium Priority
?
293 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 1000 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 1000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

777 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