# Converting String Data RSS

## 17 replies

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

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

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

May 20, 2011 06:42 AM|LINK

```select convert(decimal(4,2), replace(replace(weight, 'kg', ''), ' ','') from ahi_patient
where weight is not null
and weight <> 0```
Regards,
Dhaval Tawar
Please mark as answer if you find solution by this post

Star

14460 Points

2402 Posts

### Re: Converting String Data

May 20, 2011 06:50 AM|LINK

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

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

Star

14460 Points

2402 Posts

### Re: Converting String Data

May 23, 2011 12:06 PM|LINK

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

256 Posts

### Re: Converting String Data

May 23, 2011 12:16 PM|LINK

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

15155 Points

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

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

Member

170 Points

256 Posts

### Re: Converting String Data

May 23, 2011 12:23 PM|LINK

Good catch!  that also worked.

thanks