Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert char 19 column to integer

Posted on 1999-09-30
8
Medium Priority
?
401 Views
Last Modified: 2008-03-03
I have a column of numbers in Table A of data type char (length 19). I would like to copy the entries in this column to another table, Table B, to a column of type integer (long integer).
What would be a good way to do this?
0
Comment
Question by:vd
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 7

Expert Comment

by:simonsabin
ID: 2087862
You will need to store it as a numeric(19)

i.e
insert into tableb(col1)
select convert(numeric(19),col1) from table1
0
 

Author Comment

by:vd
ID: 2088605
There's no such thing as a numeric 19!  Is there...
0
 
LVL 2

Accepted Solution

by:
JHausmann earned 200 total points
ID: 2088926
Yes, there is. A numeric field that uses from 17-19 places of precision uses 9 bytes of storage. The maximum precision is 38, which uses 17 bytes of storage. NUMERIC is known as an "exact" numeric datatype, FLOAT and REAL are "approximate" numeric datatypes Float's can hold a maximum of 15 digits of precision, which is significantly less than the 38 places a numeric field is capable of using.

NUMERIC(precision, scale) is the same as DECIMAL (precision,scale). They are *both* present in SQL only for ANSI compatibility. Precision is used to store the total length of the number (not including the "sign") and scale can never be larger than the precision.

You have to be aware that if your data (col1) contains alphabetic characters, the conversion WILL not happen. If it's a string of digits, it will work.

simonsabin answer is/was correct, the thing I would change is the conversion function (although it does not make his answer incorrect) :

insert into tableb(col1) select convert(numeric(19,0), col1 from table1
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 2

Expert Comment

by:JHausmann
ID: 2089422
One final thing, if your the string of characters that's in your table is less than 11 digits (in the field defined as char(19)) you could do:

insert into tableb(col1)
select convert(int,col1) from table1 where isnumeric(col1) = 1
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 2090073
JHausman

I know you are new so I will let you off but be aware you should never post an answer if someones comment is correct. You should only post an answer if you have the definitive answer and no one has already said it in a comment/answer before. Or you have been asked to post an answer
0
 

Author Comment

by:vd
ID: 2091207
Thanks for your thorough explanation.
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 2091229
Thanks...
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6859627
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

704 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