Split Field Every 1K Characters
This should work:

SELECT 
    LEFT(fieldtosplit
        , CASE WHEN LEN(fieldtosplit) &gt; 1000 THEN 1000 ELSE LEN(fieldtosplit) END
    )
    , CASE WHEN LEN(fieldtosplit) &lt;= 1000 THEN '' ELSE
        SUBSTRING(fieldtosplit, 1001
            , CASE WHEN LEN(fieldtosplit) &gt; 2000 THEN 1000 
                ELSE LEN(fieldtosplit)-1000 END
        )
    END
    , CASE WHEN LEN(fieldtosplit) &lt;= 2000 THEN '' ELSE
        SUBSTRING(fieldtosplit, 2001, LEN(fieldtosplit)-2000)
    END
FROM (SELECT fieldtosplit = CAST(fieldtosplit as varchar(3000) FROM data)


I am not sure why you need to do that and it may be a bad idea. Perhaps something should be changed in your DB design in order to avoid this logic and this type of string manipulation. But I don't have enough information to be sure.

You are talking about 3000 characters and 1000 characters but you are using DATALENGTH ans 1 CAST AS VARCHAR:


LEN seems to be better than DATALENGTH since you want a number of characters, not a number of bytes
CAST does not seem useful here or perhaps you have NVARCHAR data or something wrong in the design