Re: Converting String Data

Run the select statement without any replace or trim or anything, and check all the values you get to make sure none of them has anything other than single quotes & kg. Maybe you're dealing with data that has different string, or other off characters.

Re: Converting String Data

Thanks for all of the suggestions. I have tried them all but I still get the same error message. I have scanned through the data (over 5000 rows so its tough to look at everything). I do not see anything but numbers, spaces and 'kg'. The column is defined
as varchar(8) so I am guessing anything could be there. I do see a lot of zero's and NULLs. Could that be the issue??

Re: Converting String Data

Mongol648

Thanks for all of the suggestions. I have tried them all but I still get the same error message. I have scanned through the data (over 5000 rows so its tough to look at everything). I do not see anything but numbers, spaces and 'kg'. The column is defined
as varchar(8) so I am guessing anything could be there. I do see a lot of zero's and NULLs. Could that be the issue??

Just simply run the below query to check that are there records which are not qualified for numeric..

Than after you will realize the main problem..

And than cast/convert function will work.

--return all records which are not qualified to convert/cast into numeric
--qualification based on weight data
select [weight]
from ahi_patient
where [weight] is not null
and Isnumeric(RTRIM(LTRIM(REPLACE(WeightData,'KG',''))))=0