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) > 1000
THEN LEFT(CAST(fieldtosplit AS VARCHAR(MAX)), 1000)
ELSE fieldtosplit END,'')
Best Answer
This should work:
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: