carriage return and line feeds

I have a value of 7.5 when I ask for the length (after I have trimmed it)
the value 5 is returned. Visually this is a 3. Someone suggested there may
be a carriage return and line feed (Char(10) and Char(13)) in the column.
How can I remove these if they exist?
Thanks a bunch,What is the data type? Did you try DATALENGTH? Why aren't you using a
numeric data type to store numeric data?
To remove a CHAR(13), for example, you can use REPLACE():
SELECT REPLACE(column_name, CHAR(13), '')
FROM table_name;
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:D6800B7A-C7C1-4473-9517-618E915B2198@.microsoft.com...
>I have a value of 7.5 when I ask for the length (after I have trimmed it)
> the value 5 is returned. Visually this is a 3. Someone suggested there
> may
> be a carriage return and line feed (Char(10) and Char(13)) in the column.
> How can I remove these if they exist?
> Thanks a bunch,|||"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:D6800B7A-C7C1-4473-9517-618E915B2198@.microsoft.com...
>I have a value of 7.5 when I ask for the length (after I have trimmed it)
> the value 5 is returned. Visually this is a 3. Someone suggested there
> may
> be a carriage return and line feed (Char(10) and Char(13)) in the column.
> How can I remove these if they exist?
> Thanks a bunch,
What datatype is the column?
declare @.dec decimal(5,3)
set @.dec = 7.5
select len(ltrim(rtrim(@.dec)))
...also returns 5.|||I thoguht this was an interesting behavior, so I did a little more
testing...
declare @.dec decimal(5,3)
declare @.var varchar(20)
declare @.char char(10)
set @.dec = 7.5
set @.var = CAST(@.dec AS varchar(20))
set @.char = CAST(@.dec AS char(10))
select len(ltrim(rtrim(@.dec))) as dec
,len(ltrim(rtrim(@.char))) as char
,len(ltrim(rtrim(@.var))) as var
select @.dec as dec
, @.char as char
, @.var as var
RESULTS
dec char var
5 5 5
dec char var
7.500 7.500 7.500
If the number is defined with 3 decimal places, these 3 places are stored as
zeros. Some applications just happen to display the values without trailing
zeros.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23qk9naLOGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:D6800B7A-C7C1-4473-9517-618E915B2198@.microsoft.com...
it)
column.
> What datatype is the column?
> declare @.dec decimal(5,3)
> set @.dec = 7.5
> select len(ltrim(rtrim(@.dec)))
> ...also returns 5.
>|||More fun...
DECLARE @.dec DECIMAL(5,3)
SET @.dec = 7.5
SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))|||Thats cheating.
You reduced the precision.
But here is one more, which I find more puzzling, although I recall other
languages doing strange rounding like this as well.
I can't recall WHY the rounding works like this...
DECLARE @.dec DECIMAL(5,3)
SET @.dec = 7.555
SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
, LTRIM(STR(@.dec, 6, 1))
SET @.dec = 7.55
SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
, LTRIM(STR(@.dec, 6, 1))
Results:
3 7.6
3 7.5
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyuhSwLOGHA.2320@.TK2MSFTNGP11.phx.gbl...
> More fun...
> DECLARE @.dec DECIMAL(5,3)
> SET @.dec = 7.5
> SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
>|||> Thats cheating.
> You reduced the precision.
Well, the extra characters are leading, not trailing. Compare LTRIM() and
RTRIM()...
A|||Aaron and Co.,
Thanks for your replies. Unfortunately, the datatype can't be stored as a
number because results that come in are from many different sources
(Laboratories, Radiology etc) and have different formats. The specific
result I am looking at will always return values like 10.3 or 7.5.
The problem is that for some reason, after I strip the extraneous characters
away(which I have done with ease), I try to cast these values as decimal and
it doesn't work.
Here is code and raw data...it's got me stumped...
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.
This led me to look at the string lengths of these characters and I
discovered that a few values had what appeared to be spaces in them even
after they were trimmed.
Thanks to all who give their .02.
"Aaron Bertrand [SQL Server MVP]" wrote:

> More fun...
> DECLARE @.dec DECIMAL(5,3)
> SET @.dec = 7.5
> SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
>
>|||In your sample output there is an extra space before and after the value in
question.
I know your code is removing spaces, but I'm not sure what accounts for the
values in the output.
Can you post your table DDL? What datatype is decodedvalue?
I suspect you have tabs rather than carriage returns, but try this code...
convert(varchar,replace(replace(replace(
replace(replace(@.dec,'
',''),'> ',''),'%',''),char(10),''),char(13),'')c
har(9),''))
Char(10) and Char(13) are a carriage return and line feed (I can never
recall which is which).
Char(9) is a tab character.
Rather than removing the characters you dont want, change the code to keep
only the characters you do want. I'll see if I can track down a sample of
this, but someone else will probably beat me to it.
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:1125946A-8B58-4424-9F4D-C1FBE98CDA6B@.microsoft.com...
> Aaron and Co.,
> Thanks for your replies. Unfortunately, the datatype can't be stored as a
> number because results that come in are from many different sources
> (Laboratories, Radiology etc) and have different formats. The specific
> result I am looking at will always return values like 10.3 or 7.5.
> The problem is that for some reason, after I strip the extraneous
characters
> away(which I have done with ease), I try to cast these values as decimal
and
> it doesn't work.
> Here is code and raw data...it's got me stumped...
> 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(rep
lace(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.
>
> This led me to look at the string lengths of these characters and I
> discovered that a few values had what appeared to be spaces in them even
> after they were trimmed.
> Thanks to all who give their .02.
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> Char(10) and Char(13) are a carriage return and line feed (I can never
> recall which is which).
I have two ways of remembering it:
(a) in VB, the constant is named vbCrLf (carriage return, line feed)
(b) they come in inverse order
So, CHAR(13) is carriage return, CHAR(10) is line feed. In some
environments, this is the order it has to be in; in some environments, the
opposite is true. And to make things even more fun, in some environments,
you can have them in either order.
At least that's how I loosely remember it.
A