data conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

data conversion

Author

Topic

sqlkid
Starting Member

41 Posts

Posted - 2009-07-09 : 08:12:37

One column in my table has data with 5 decimal points eg, 27.43152. I am importing the data. When I try to convert the data to decimal type using the following commands :
alter table T
alter column col003 decimal (10,5)

I get the following error msg :
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
The statement has been terminated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-09 : 08:16:38

You have bad data on that column. See what this returns

select col003 from t
where col003 like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

sqlkid
Starting Member

41 Posts

Posted - 2009-07-09 : 08:25:51

quote:
Originally posted by madhivanan

You have bad data on that column. See what this returns

select col003 from t
where col003 like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

I have got the list of all entries in that column

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-09 : 08:28:22

quote:
Originally posted by sqlkid

quote:
Originally posted by madhivanan

You have bad data on that column. See what this returns

select col003 from t
where col003 like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

I have got the list of all entries in that column

Can you post some of the results?

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-09 : 08:29:08

I know it is not the best way but in this case try to find bad data using:
where isnumeric(col003) <> 1

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-09 : 08:29:41

Try this too

select col003 from t
where col003 like '%[^0-9.]%'

Madhivanan

Failing to plan is Planning to fail

sqlkid
Starting Member

41 Posts

Posted - 2009-07-09 : 08:30:29

[/quote]
Can you post some of the results?

Madhivanan

Failing to plan is Planning to fail
[/quote]

6.6968
5.8938
2.5473
5.8938
6.6968
5.8938
5.8974
6.6968

sqlkid
Starting Member

41 Posts

Posted - 2009-07-10 : 03:49:53

quote:
Originally posted by webfred

I know it is not the best way but in this case try to find bad data using:
where isnumeric(col003) <> 1

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanx webfred - I have identified and removed the bad data. But now i have entire rows with no data for any column. how can i delete these ?
i tried

DELETE
FROM T
WHERE Col001 IS Null
(repeated for all columns) - but the result is 0 rows affected

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 04:07:11

DELETE FROM T
WHERE COALESCE(Col1, Col2, Col3) IS NULL

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources