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

You would have to cast it to a VARCHAR(MAX) first.

Answer 2

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.