was working with a quick query to find some data in a netxt field containing a comma-seperated list of countries, so i wrote:
select id, countries from tablea where cast(countries as varchar) LIKE '%mozambique%'
this didn't return the number of rows i expected so i investigated further. it seems that using cast() in this way (without specifiying a length) creates a varchar of default length. if a string in my ntext fields is longer than this length thestring is terminated. hmm, fun.
my curiosity got the better of my so i checked to see what the default length actually is.
select len(cast(countries as varchar)), datalength(cast(countries as varchar)), countries
from tablea
returns 30 for both len() and datalenght(). 30? why 30?? what an decidly odd default.
-edoode
[update] thanks to my friends at stackoverflow i now see the official ms doc stating that the default is 30. I still wonder about the why though...