How do you use LEFT on a NTEXT SQL Server Column?

Question

How do you use the LEFT function (or an equivalent) on a SQL Server NTEXT column?

Basically I'm building a GridView and I just want to return the first 100 or so characters from the Description column which is NTEXT.

Answer 1

SELECT CAST(ntext_col AS nvarchar(100)) as ntext_substr FROM ...

[EDIT] Originally had it returning LEFT(N,100) of CAST to nvarchar(MAX), CASTing will truncate and since LEFT is wanted, that is enough.

Answer 2

You can use the SUBSTRING function, which "returns part of a character, binary, text, or image expression":

SUBSTRING ( value_expression , start_expression , length_expression )

So, to select the first 100 characters from your Description NTEXT column, you would use something like the following:

SELECT SUBSTRING(Description, 1, 100) as truncatedDescription FROM MyTable;