[SOLVED] SQL: Take the last 4000 characters from text

SQL: Take the last 4000 characters from text

I have a text column that I want to see if it has larger than 4000 characters. If it does have more than 4000 characters, then start at the right most character and go back 4000 characters.

I realize that the following code is not correct. But it is a start.

select 
case 
when datalength(column1) > 4000 then
right(column1, 4000)
case datalength(column1) <= 4000 then 
column1
end 
from table_1

Solution

In SQL Server:

SELECT CASE
    WHEN (DATALENGTH(column1) > 4000)
    THEN SUBSTRING(column1, DATALENGTH(test) - 3999, DATALENGTH(column1))
    ELSE column1
END
FROM table_1

Not fantastic, since you're recalculating DATALENGTH(column1) three times.

EDIT

Calculating only once using a CTE:

WITH CTE AS
(
    SELECT DATALENGTH(column1) AS LENGTH, column1 FROM table_1
)
SELECT CASE
    WHEN (LENGTH > 4000)
    THEN SUBSTRING(column1, LENGTH - 3999, LENGTH)
    ELSE column1
END
FROM CTE