linefeeds in text fields extracted with substring

Im running a query like
select * from MyTable where SUBSTRING(MyColumn,1,300)='searchtext'
MyColumn is th the text type.
The text in MyColumn contains a lot of tabulator characters (TAB),
carrige return (CR) and linefeed (LF) characters.
Sometimes (but not often) when I run the query, even in the Query
Analyser, I get the wrong result. I get too few characters, even though
I know they are in the database. (If I read the field with out the
substring, I can see everything).
The error is consistent - either a record works as it should, or it
fails to.
All answers are very welcome.
Thomas K
select @.@.version
----
----
----
--
-
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
(1 row(s) affected)I think you did not handle the Tab, CR and Linefeed. The records that
dont have these will work otherwise fail
Madhivanan|||Check your 'maximum characters per column' setting in Query Analyzer under
Tools --> Options --> Results. This may be set too small and truncate the
value.
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas Holme" <seiofecco@.hotmail.com> wrote in message
news:ONdDTThJFHA.1096@.tk2msftngp13.phx.gbl...
> Im running a query like
> select * from MyTable where SUBSTRING(MyColumn,1,300)='searchtext'
> MyColumn is th the text type.
> The text in MyColumn contains a lot of tabulator characters (TAB), carrige
> return (CR) and linefeed (LF) characters.
> Sometimes (but not often) when I run the query, even in the Query
> Analyser, I get the wrong result. I get too few characters, even though I
> know they are in the database. (If I read the field with out the
> substring, I can see everything).
> The error is consistent - either a record works as it should, or it fails
> to.
>
> All answers are very welcome.
> Thomas K
>
> select @.@.version
> ----
----
----
--
--
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> (1 row(s) affected)sql