1

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
Bridge
  • 29,818
  • 9
  • 60
  • 82
Jeremy F.
  • 1,778
  • 11
  • 51
  • 86
  • 4
    Which RDBMS is this for? – p.campbell May 07 '12 at 19:47
  • 1
    Just realized he was dealing with text and not varchar. You cant use length – JonH May 07 '12 at 19:53
  • See this http://stackoverflow.com/questions/1219261/t-sql-selecting-top-n-characters-from-a-text-or-ntext-column – JonH May 07 '12 at 19:55
  • Ok fixed using datalength and substring. – JonH May 07 '12 at 19:56
  • 1
    **What** database system, and which version?? **SQL** is just the Structured Query Language - a language used by many database systems - SQL is **NOT** a database product... stuff like this is very often vendor-specific - so we really need to know what database system you're using.... – marc_s May 07 '12 at 21:05

3 Answers3

1

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
zimdanen
  • 5,508
  • 7
  • 44
  • 89
0

This works in Oracle. Here is the documentation on Substr: http://psoug.org/definition/SUBSTR.htm

select (case when length(column1) > 4000 
             then substr(column1, -4000) 
             else column1 
        end) 
from table_1;
Dilshod Tadjibaev
  • 1,035
  • 9
  • 18
-1

In SQL Server

SELECT
  CASE WHEN datalength(MyColumn) > 4000 THEN SUBSTRING(REVERSE(MyColumn), 1, 4000) ELSE MyColumn END AS MyGenCol
JonH
  • 32,732
  • 12
  • 87
  • 145