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
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
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??
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
Shetty Abhij...
Contributor
2180 Points
328 Posts
Re: Converting String Data
May 20, 2011 06:23 AM|LINK
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
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...
Dhaval Tawar
Participant
1462 Points
321 Posts
Re: Converting String Data
May 20, 2011 06:42 AM|LINK
Dhaval Tawar
Please mark as answer if you find solution by this post
yrb.yogi
Star
14460 Points
2402 Posts
Re: Converting String Data
May 20, 2011 06:50 AM|LINK
.Net All About
Mongol648
Member
170 Points
255 Posts
Re: Converting String Data
May 23, 2011 11:56 AM|LINK
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??
yrb.yogi
Star
14460 Points
2402 Posts
Re: Converting String Data
May 23, 2011 12:06 PM|LINK
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.
.Net All About
Mongol648
Member
170 Points
255 Posts
Re: Converting String Data
May 23, 2011 12:16 PM|LINK
This finally worked:
Thanks for all of the help!
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: Converting String Data
May 23, 2011 12:20 PM|LINK
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 ?
Kumar Harsh
Mongol648
Member
170 Points
255 Posts
Re: Converting String Data
May 23, 2011 12:23 PM|LINK
Good catch! that also worked.
thanks