Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post May 23, 2011 12:23 PM by Mongol648
May 19, 2011 01:49 PM|LINK
I have weight data stored in a SQL Server table as varchar data type. Generally the data is stored like '57.25 kg'. I am trying to extract the 57.25.
This is what I have at the moment:
select convert(numeric, (rtrim(STR(weight,1,(charindex(' ',weight)))))) from ahi_patient
where weight is not null
and weight <> 0
I am getting the following error:
Syntax error converting the varchar value '79.38 kg' to a column of data type int.
Any suggestions on how to get past this?
May 19, 2011 02:07 PM|LINK
I think you need to use REPLACE to replace all occurences of "kg" and single quote; and replace them with a blank, so in essence removing them. Then they can be converted.
May 19, 2011 03:14 PM|LINK
DECLARE @weight VARCHAR(10)
SET @weight = '''57.25 Kg'''
SELECT CAST(REPLACE(REPLACE(lower(@weight), '''', ''), 'kg', '') AS NUMERIC(16,2))
SELECT CAST(REPLACE(REPLACE(lower(weight), '''', ''), 'kg', '') AS NUMERIC(16,2))
WHERE weight is not null
AND weight <> 0
May 19, 2011 04:35 PM|LINK
I tried this:
select cast(replace(replace(weight,'''', ''), 'kg', '') as numeric(3,2)) from ahi_patient where weight is not null and weight <> 0
but still end up with the same error.
I did try with (lower) as well with the same result.
Did I miss something?
May 19, 2011 05:02 PM|LINK
Maybe the space character is causing this; so try: cast(RTRIM(replace(replace(weight,'''', ''), 'kg', ''))
May 19, 2011 05:24 PM|LINK
Using that, the error is a little less specific:
Error converting data type varchar to numeric.
May 19, 2011 05:34 PM|LINK
May 19, 2011 07:02 PM|LINK
I have never had to use PRINT in a select and I can't seem to find an appropriate example anywhere. Can you help?
May 19, 2011 07:18 PM|LINK
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.
May 20, 2011 05:53 AM|LINK
I think cast/convert is not required.Simply replace will do.
Secondly if you cast it to numeric then it may round it.to avoid this use decimal (4,2)
Is there some other alpha charector other than 'kg' ?
@i varchar (20)
set @i='57.25 kg'