Split Field Every 1K Characters I have a field that can hold up to 3000 characters.  I need to split this field into 3 individual fields split1, split2, split3, I have successfully been able to figure out how to get the beginning, but grabbing the middle and end I am stuck on.  How would I capture the characters from 1001 to 2000 and from 2001 to 3000?

This is what I use to capture from 0 to 1000 how would I capture the data for split2, and split3

split1 = COALESCE(CASE WHEN DATALENGTH(fieldtosplit) &gt; 1000 
         THEN LEFT(CAST(fieldtosplit AS VARCHAR(MAX)), 1000) 
         ELSE fieldtosplit END,'')
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