Solved

# Cast problem string into integer

Posted on 2008-11-10
1,448 Views
Hello,

Attached code is giving me an error. Could somebody explain me why?
in table 'Data" [close] is a string value. In table 'test' [Close] is a integer

Regards,
Dennis
``````insert into test ([Date],cast[Close] AS integer)

select [DATE],[close]  from data
``````
0
Question by:Schuttend

LVL 59

Expert Comment

You have the CAST in wrong place:

INSERT INTO test([Date], [Close])
SELECT [DATE], cast([Close] AS INT)
FROM data
0

LVL 9

Expert Comment

missing a '(' for cast
0

LVL 59

Expert Comment

If number is bigger than 2 billion (over 10 characters or 10 characters beginning with 3+), then you may to use CAST([Close] AS BIGINT).

http://msdn.microsoft.com/en-us/library/ms187745.aspx
0

LVL 9

Expert Comment

mwvisa1 is always quick and precise
0

Author Comment

Might be, but code is incorrect ;) Please see attached pic
casterror.jpg
0

LVL 9

Expert Comment

coz it's a double not integer
0

LVL 59

Expert Comment

Your data has a decimal in it.

INSERT INTO test([Date], [Close])
SELECT [DATE], cast([Close] AS MONEY)
FROM data
0

LVL 59

Expert Comment

If the datatype of close if INT, then you will have to do this:

INSERT INTO test([Date], [Close])
SELECT [DATE]
, ROUND(cast([Close] AS MONEY), 0)
FROM data

Hope that helps.
0

LVL 9

Expert Comment

or

cast(unit_price as numeric(10,2))  or whatever precision it should be
0

LVL 59

Expert Comment

Correct.  The point is you cannot do the cast in the column definition of the insert statement, it must be done to the value and the value must be valid for the type you are casting to.  Therefore, for an INT, you cannot cast 1666.53 since it is not a whole number.

Hopefully that makes sense.
0

Author Comment

The destination field is now format decimal (10,10). However I get an error. Any reason for this?

regards,
Dennis
cast2.jpg
0

LVL 9

Expert Comment

decimal[(p[, s])] and numeric[(p[, s])]

normally p > s

try decimal(20,10)
0

LVL 59

Expert Comment

What are you trying to do?

If you control the target column data type, then figure out what you want the data to represent and the data type will be self explanatory.  For example, if close is say stock price at last bell then MONEY would make sense or a DECIMAL(p, 4) since dollars go out to 4 places max typically.

Regards,
Kevin
0

Author Comment

I have multiple strings, which need to be converted before calculations can be made.
The problem is that sometimes the string contains a money values (1112.45 for example) But sometimes it contains values like 0.993432342034. What would be the correct format to choose for the destination field??
0

LVL 59

Expert Comment

The DECIMAL(20, 10) should fit both of those cases.

http://msdn.microsoft.com/en-us/library/ms187746.aspx

Best regards,
Kevin
0

Author Comment

Hi,
This is probably more difficult then I thought.
The decimal (20,10) does work on tables open,high,low,close,systemident
But not on [delta], [signal]. How can this be?
I changed the format field to decimal 30,22 to give it a try. But as you can see, it still generates a error??
cast3.jpg
cast4.jpg
0

LVL 9

Expert Comment

run this and see if you get any record

SELECT [DATE], [Close]
FROM data
where isnumeric([Close]) = 0
0

Author Comment

no errors, but no records are returned...
0

LVL 9

Expert Comment

you may have to find which record(s) causing the problem

can you add more conditions to locate the record(s)

SELECT ...
FROM data
where [date] between .. and ..

0

LVL 26

Expert Comment

I recommend you to change all the columns from decimal type to double and try it.  The date column you should wrap it in conver and also ltrim-rtrim the strings to get rid of spaces just in case. You don't need to cast in the select statement as it will be casted automatically if the values are in corect format in regards to the type. You have however to convert the date as being in europe you might have different settings on the server that don't recognize mm/dd/yyyy format by default:

``````insert into

(

date,

[close]

)

SELECT

convert(datetime, ltrim(rtrim([DATE])), 101),

ltrim(rtrim([Close]))

FROM

data
``````
0

LVL 59

Expert Comment

Think you are bouncing to new question here, so just to clarify, the DECIMAL(20, 10) worked for [Close] so that is not an issue.

>>But not on [delta], [signal]. How can this be?

You will need to check data for [delta] and [signal] using james' suggestion.

SELECT *
FROM data
where isnumeric([delta]) = 0 OR isnumeric([signal]) = 0

Post the data you find.
0

Author Comment

Hi,

As you can see no record output

cast5.jpg
0

LVL 59

Expert Comment

SELECT *
FROM data
WHERE LEN([delta]) > 20 OR LEN([signal]) > 20

What you need to look for whether the above gets you the value or not is the largest decimal value from your data now that you have confirmed that all the data is numeric.
0

LVL 59

Expert Comment

Ultimately, you can just try DECIMAL(38, 28).  If values have decimals greater than that then adjust accordingly.

You may have to look at FLOAT or REAL as welll.
http://msdn.microsoft.com/en-us/library/ms173773.aspx
0

LVL 9

Expert Comment

and try this

select max(len(substring( [delta], charindex('.', [delta]), 100))) as delta_len1
, max(len(substring( [signal], charindex('.', [signal]), 100))) as signal_len1
, max(len([signal]))
, max(len([delta]))
from data
0

Author Comment

Please find attached the result of jamesqu
len.jpg
0

LVL 59

Expert Comment

Try then DECIMAL(38, 19).  If that doesn't work, then it means you have values that have more than 19 places before the decimal as well as values with 19 places after the decimal and 38 is the max places on DECIMAL from my understanding; therefore, you will have to move to FLOAT.
0

LVL 40

Expert Comment

Is that what you want?
0

LVL 9

Expert Comment

check this,

if you have large data, you may have to use varchar instead of decimal, and do your conversion in your client programming language if you can
``````select max(len(substring( [delta], charindex('.', [delta]), 100))) as delta_len1

, max(len(substring( [signal], charindex('.', [signal]), 100))) as signal_len1

, max(len(substring( [delta], 1, charindex('.', [delta]))) as delta_len2

, max(len(substring( [signal], 1, charindex('.', [signal]))) as signal_len2

, max(len([signal]))

, max(len([delta]))

from data
``````
0

LVL 26

Expert Comment

Schuttend,

Why don't you try my suggestion with type double for your numeric columns instead of decimal. You could build a ne table to test. Double is the most flexible numeric type and will deal with all kind of values even those with exponent in them like  1.3E02, like I noticed you had few values. The decimal type will invariably fail on those values.

Use double and then try my query, again here:
``````insert into

(

date,

[close]

-- ... rest of the columns here

)

SELECT

convert(datetime, ltrim(rtrim([DATE])), 101),

ltrim(rtrim([Close]))

-- ... rest of the columns here but stil use ltrim-rtrim wrap

FROM

data
``````
0

LVL 9

Expert Comment

Zberteoc,

double is Approximate Numerics, it lost information
0

LVL 26

Expert Comment

Are you suggesting that double is a useless type? This is a first to me.

Schuttend, just try it. You can also use float, not necessarily double.
0

LVL 9

Expert Comment

it's not useless, but it has its limitation

if possible, if may give a try with some value like 1.123456789123456789

store this into a double variable and check the value
0

Author Comment

Indeed that's my experience as well with double. However is also accounts for using floats. Am I correct?
0

LVL 26

Expert Comment

I actually meant float. Double doesn't even exist on sql.
0

LVL 59

Expert Comment

I was going to keep quiet since I figured you knew something I didn't. ;)
Yes SQL implements double precision using FLOAT(53) datatype.

0

LVL 26

Expert Comment

Yesterday I tried to post something but was lost because of problems with the website. Here:

I checked and actually decimal type works fine with values like 1.123E-02 and from the picture attached some posts earlier the values look OK in the Data table. I suspect that the problem for conversions comes from the source file that was loaded to Data table in varchar columns. It might be possible that some invisible special characters like tab or garbage are also loaded with the number values. They are there but are not visible and cause the conversion error.

In order to check this you need a text editor to open the original source file with and make the special character visible. Try Wordpad or even Word and click on the Pi button. If you have something else than carriage return and line feed at the end of the line or if you have tabs within the comma delimiters there is your problem. You need to clean the source file and load it again.

There is nothing else I can see for now.
0

LVL 51

Accepted Solution

hey schuttend, didn't we already cover this off in the other post ? in so much as the signal and delta were exponentiated numbers which you fiust have to import as float and then convert to decimal - believe we ended up with something like convert(decimal(36,20), convert(float, signal) ) - even tidied up the "float" rounding business to be real decimals....

Or is this something different ?
0

LVL 51

Expert Comment

As a memory jogger - nd might want to close off a couple of these (or reply to them) see : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q-23889285-Bulk-INSERT-with-different-field-types.html
0

Author Closing Comment

Thanks...
0

LVL 26

Expert Comment

I didn't understand what happened here, Mark Wills got the points twice for the same question? I don't blame Mark here but I kinda feel that I just wasted my time on this question simply because the author had a lack of memory. And Mark had no input on this question whatsoever.

Beside that I also suggested to make the import as float and then do whatever they needed with it and, regardless, this is a totally different question than the referenced one.
0

LVL 51

Expert Comment

Hi zberteoc,

Might seem that way, it is just that there was another problem (a couple actually) that started out as discrete boinafide questions, and, in answering the import one, it did get into formatting columns. Ended up writing an import / data load which also took care of formats. So, I think I did have a lot of input into resolving this question, just not neccessarily on this thread (if that makes sense). My answer in this thread is a legitimate answer to this question, and in providing it, did raise the question if this is a different problem. I was concerned.

In fairness to schuttend, this data file appears to have been an absolute pain, and what seems to be different (albeit related) questions, happened to get the  answers slightly differently to the way in which they were asked.

In fact, I do think schuttend should be commended - instead of one question morphing into several different Q+A - actually thought about how to segment a problem into discrete questions - doesn't often happen that way...  And here is irony for you, it is the experts that have morphed a few answers into one !

So, I do not think it was a case of getting double-up points as much as being able to provide double-up answers. Probably does not relieve the frustration, but thought an explanation was in order.

Cheers,
Mark Wills
0

Author Comment

Hi Zberteoc,

I'm sorry you feel this way because. I sure appreaciate your great work. I am still working on your comments at the question available below:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23883001.html

This issue is still not resolved. I am even willing to offer you 1000 points more if this issue can finally be solved. Sharath has been working on this as well, but he is not responding anymore.
I am trying to break up problems and post them. However sometimes they are linked together for some reason.
If you please can solve my problem at the link below I think we can arrange the extra 1000 points somehow.

Regards,
Dennis
0

## Featured Post

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+) Â  Â  as used in Oracle; Â  Â  *= Â  Â  =* Â  Â as used in Sybase â€¦
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alonâ€¦
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoftâ€™s cloud platform, you know that you will need to create a corporate email signature for your Office 365â€¦
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods youÂ´d like to investigate in more detail.  The methods are covered in more detail in oâ€¦