Converting String Data RSS

• • Reply
  

  Mongol648

  Member

  170 Points

  255 Posts

  Converting String Data

  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?

  thanks

• • Reply
  

  MetalAsp.Net

  All-Star

  112032 Points

  18231 Posts

  Moderator

  Re: Converting String Data

  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.

  C# <--> VB.Net Translator  |  jQuery Widgets
• • Reply
  

  sandeepmitta...

  Contributor

  6767 Points

  1057 Posts

  Re: Converting String Data

  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))

  Accordingly,

  SELECT	CAST(REPLACE(REPLACE(lower(weight), '''', ''), 'kg', '') AS NUMERIC(16,2))
  FROM	ahi_patient
  WHERE	weight is not null
  AND		weight <> 0

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
• • Reply
  

  Mongol648

  Member

  170 Points

  255 Posts

  Re: Converting String Data

  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?

   

  • Edited by Mongol648 on May 19, 2011 04:59 PM
• • Reply
  

  MetalAsp.Net

  All-Star

  112032 Points

  18231 Posts

  Moderator

  Re: Converting String Data

  May 19, 2011 05:02 PM|LINK

  Maybe the space character is causing this; so try: cast(RTRIM(replace(replace(weight,'''', ''), 'kg', ''))

  C# <--> VB.Net Translator  |  jQuery Widgets
• • Reply
  

  Mongol648

  Member

  170 Points

  255 Posts

  Re: Converting String Data

  May 19, 2011 05:24 PM|LINK

  Using that, the error is a little less specific:

   

  Error converting data type varchar to numeric.

• • Reply
  

  MetalAsp.Net

  All-Star

  112032 Points

  18231 Posts

  Moderator

  Re: Converting String Data

  May 19, 2011 05:34 PM|LINK

  Add a bunch of PRINT statements to print out the various parts of that select. This way you can analyze the parts and see if/where something is not right.
  C# <--> VB.Net Translator  |  jQuery Widgets
• • Reply
  

  Mongol648

  Member

  170 Points

  255 Posts

  Re: Converting String Data

  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?

   

  thanks!

• • Reply
  

  MetalAsp.Net

  All-Star

  112032 Points

  18231 Posts

  Moderator

  Re: Converting String Data

  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.

  C# <--> VB.Net Translator  |  jQuery Widgets
• • Reply
  

  KumarHarsh

  All-Star

  15133 Points

  3647 Posts

  Re: Converting String Data

  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' ?
  
  

  Declare @i varchar (20)
  set @i='57.25 kg'
  Select stuff(@i,patindex('%[a-zA-Z]%',@i)-1,len(@i),'')

  Warm Regards
  Kumar Harsh