17 replies

Last post May 23, 2011 12:23 PM by Mongol648

Contributor

2180 Points

328 Posts

Re: Converting String Data

Try this:

select convert(numeric(18, 2), REPLACE(weight, 'kg',''))
from ahi_patient
where weight is not null
and ISNUMERIC(convert(numeric(18, 2), REPLACE(weight, 'kg','')))=1
and weight <> 0

I think there are some records with data that can't be converted to numeric so and ISNUMERIC(convert(numeric(18, 2), REPLACE(weight, 'kg','')))=1 in where clause will take care of it

Thanks...AJ...Regards,
Shetty Abhijit Jayaram.
www.actrounce.com

Mark the Post as ANSWER ,if the post helped you...

If set a GOAL become RESTLESS till achieve ONE...

Participant

1462 Points

321 Posts

Re: Converting String Data

```select convert(decimal(4,2), replace(replace(weight, 'kg', ''), ' ','') from ahi_patient
where weight is not null
and weight <> 0```
Regards,
Dhaval Tawar

Star

14460 Points

2402 Posts

Re: Converting String Data

```Declare @WeightData Table
(
Id int identity,
WeightData varchar(100)
)
insert into @WeightData
Select '  57.25 kg' UNION ALL
Select '   67.25 kg' UNION ALL
Select ' 98.25  kg' UNION ALL
Select '1520.25 kg' UNION ALL
Select '1830.25 kg' UNION ALL
Select ' 57.25 kg' UNION ALL
Select '1.25   kg' UNION ALL
Select '11.25   kg  ' UNION ALL
Select '            115.25                          kg  '

Select Id,
Cast(RTRIM(LTRIM(REPLACE(WeightData,'KG',''))) As Decimal(18,2)) As WeightData
From @WeightData

--output
--Id WeightData
--1 57.25
--2 67.25
--3 98.25
--4 1520.25
--5 1830.25
--6 57.25
--7 1.25
--8 11.25
--9 115.25```

• Marked as answer by Mongol648 on May 23, 2011 12:44 PM

Member

170 Points

255 Posts

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

Star

14460 Points

2402 Posts

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

Member

170 Points

255 Posts

Re: Converting String Data

This finally worked:

```Select
Cast(RTRIM(LTRIM(REPLACE(Weight,'kg',''))) As Decimal(18,2)) As WeightData
From ahi_patient
where weight is not null
and weight <> ''```

Thanks for all of the help!

All-Star

15133 Points

3647 Posts

Re: Converting String Data

wht will happen if you remove cast.

Select RTRIM(LTRIM(REPLACE(Weight,'kg','')))  As WeightData
From ahi_patient
where weight is not null
and weight <> ''

why include un-neccessary thing ?

Warm Regards
Kumar Harsh
• Marked as answer by Mongol648 on May 23, 2011 12:44 PM

Member

170 Points

255 Posts