July 5, 2012 at 12:15 pm
Probably an easy one but I have a table (TABLE A) which is nserting data into another table (TABLE B).
The data types on the tables are not in sync, I get an error stating that char cannot be converted to money.
Obviously there is some data in the column which is not a numeric value. Can I simply just use the ISNUMERIC function.
July 5, 2012 at 12:22 pm
Bobby Glover (7/5/2012)
Probably an easy one but I have a table (TABLE A) which is nserting data into another table (TABLE B).The data types on the tables are not in sync, I get an error stating that char cannot be converted to money.
Obviously there is some data in the column which is not a numeric value. Can I simply just use the ISNUMERIC function.
Maybe...unfortunately the IsNumeric function is somewhat misleading in its name. To paraphrase Gail, it should be named IsValueSomethingThatCouldPossiblyBeConvertedToAnyOfTheNumericishDataTypes.
It will return 1 for a lot of values that initially don't make sense.
For example:
select ISNUMERIC('1.3E12')
select ISNUMERIC('$43,123,321.32')
Both will return 1 but neither appear visually to be valid numbers in their own right.
To address your issue, you might start with finding those rows where IsNumeric(yourcolumn) = 0. If you have a small number of those you could fix those first and try again.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 6, 2012 at 2:43 am
I found my bad data, they sem ti be prefixed with '£'.
How do I remove all of these characters from the row.
LTRIM?
July 6, 2012 at 2:47 am
The replace function will do what you need
UPDATE TableA SET Col1 = REPLACE(Col1,'£','')
July 6, 2012 at 3:41 am
Bobby Glover (7/5/2012)
Probably an easy one but I have a table (TABLE A) which is nserting data into another table (TABLE B).The data types on the tables are not in sync, I get an error stating that char cannot be converted to money.
Obviously there is some data in the column which is not a numeric value. Can I simply just use the ISNUMERIC function.
Hi Bobby
The folks helping you with this would have a much easier job if you could post the ddl for the two tables (CREATE TABLE...) and some sample data for Table A.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 6, 2012 at 4:22 am
i see you have identified the non numeric char, not sure how you did it, but here is one way to identify rows that has any none numeric characters
where PATINDEX('[^0-9]%',collumn)>0
***The first step is always the hardest *******
July 6, 2012 at 4:49 am
Cheers folks, Chris I will post mo. info in future.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply