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
Medium Priority
401 Views
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
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
• 3
• 2
• 2
• +1

LVL 7

Expert Comment

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

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

LVL 2

Accepted Solution

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

LVL 2

Expert Comment

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

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

ID: 2091207
0

LVL 7

Expert Comment

ID: 2091229
Thanks...
0

LVL 1

Expert Comment

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

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.
Suggested Courses
Course of the Month7 days, 4 hours left to enroll