Cast varchar to decimal

I am losing my hair...and my mind...
Is there any reason why I wouldn't be able to cast a varchar value of say
7.8 to decimal?
I have a whole bunch of lab results that come with a bunch of garbage in the
result column. I have stripped it away so that it is only a format
[1-x].[0-9]. I want to make it a number so I can identify High and low value
s
for each patient.
Am I missing something...besides my mind?
Thanks in advance,Don't see any problem in achieving what you want.
Can you post a sample to exactly understand what your issue is?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:CC2448DB-9147-4391-BBDA-7891A6E3294F@.microsoft.com...
>I am losing my hair...and my mind...
> Is there any reason why I wouldn't be able to cast a varchar value of say
> 7.8 to decimal?
> I have a whole bunch of lab results that come with a bunch of garbage in
> the
> result column. I have stripped it away so that it is only a format
> [1-x].[0-9]. I want to make it a number so I can identify High and low
> values
> for each patient.
> Am I missing something...besides my mind?
> Thanks in advance,
>
>|||Here is the code... pretty straight forward....
There is something still contained in the string that is messing up the
cast. (See raw data below code)
select top 7
patientid,
decodedvalue,
convert(varchar,replace(replace(replace(
decodedvalue,'
',''),'>',''),'%','')) value,
-- cast(convert(varchar,replace(replace(r
eplace(decodedvalue,'
',''),'>',''),'%','')) as float) value,
len(replace(replace(replace(decodedvalue
,' ',''),'>',''),'%','')) str_length
from
#diabetes_results
where
decodedvalue like '%.%'
order by
patientid
58 6.8 % 6.8 3
58 7.6 % 7.6 3
58 6.7 % 6.7 3
58 7.1 % 7.1 3
58 6.2 % 6.2 3
168 7.5 % 7.5 3
168 7.5 7.5 5
Note the length of '5' in the final record though it is obvious the length
should be 3. I trimmed the column of spaces but they remain.
Any thoughts?
Thanks in advance,
"SriSamp" wrote:

> Don't see any problem in achieving what you want.
> Can you post a sample to exactly understand what your issue is?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:CC2448DB-9147-4391-BBDA-7891A6E3294F@.microsoft.com...
>
>|||168 7.5 7.5 5
Perhaps those aren't spaces - in fact I think they are one carriage return
and one line feed character. How are these values inserted? You should
prevent illegal values from being entered at all.
In the mean time - remove char(13) and char(10) from the string.
ML
http://milambda.blogspot.com/