|
|
Posted by Hugo Kornelis on 10/14/05 00:05
On Thu, 13 Oct 2005 15:50:35 GMT, Mike wrote:
>Ok I have another problem with some of the data. I did not notice this but
>some of the columns where nvarchar data types and the math is not working on
>them. I tried to change the data type to decimal but Enterprise Manager is
>giving me and error saying it can not convert the data type.
>
>The table I am working was imported and the data types were nvarchar but I
>managed to change them to varchar data types but I need them to be numeric
>or decimal data types so that the math will work.
>
>Can any one tell how to change these?
Hi Mike,
If you are 100% sure that all values in the data can be converted to
numeric datatype, you can use ALTER TABLE:
ALTER TABLE MyTable
ALTER COLUMN MyColumn new_datatype
If you do have bad data, you'll have to find and correct that first.
This query might help you find the dirty data:
SELECT something
FROM MyTable
WHERE REPLACE(REPLACE(MyColumn,'.',''),'-','') LIKE '%[^0-9]%'
OR MyColumn LIKE '%.%.%'
OR MyColumn LIKE '_-%'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Navigation:
[Reply to this message]
|