Advanced String Split in SQL

Sometimes in a data migration project, we need to split a string into substrings with a maximum length required. This happens when the maximum length of a string stored in the target database is smaller than the maximum length of a string stored in the source database.

In addition, often it is also required that we can’t split at the middle of a word (i.e. no broken words at the end of the substrings).

The following T-SQL code demonstrates how we can achieve this – splitting a source string into substrings with a maximum length of 132 characters with no broken words at the end of the substrings. You can copy and paste this code to MS SQL Server Studio to have a run.

DECLARE @str NVARCHAR(MAX) = 'Smart Home systems that can help track and localize objects within the home have been an important part of Consumer Electronics research in recent years. In practical terms, these systems can help people find certain things, such as glasses, wallets, keys, etc., and it is often troublesome to look for them.'  

DECLARE @MAXLEN int = 132;	-- The maximum length of a substring that is split from the original string
DECLARE @newStr varchar(132);		
DECLARE @tmp varchar(132);			 
DECLARE @chr varchar(1);			
DECLARE @index int;

print 'THE ORIGINAL STRING IS BELOW:';
print @str;
print ''

print 'THE ORIGINAL STRING IS SPLITTED TO BELOW SUBSTRINGS:';

SET @str = LTRIM(RTRIM(@str));
IF (LEN(@str) > @MAXLEN) -- If longer than 132 characters then split
	BEGIN
		WHILE (LEN(@str) > @MAXLEN) 
		BEGIN
			SET @chr = SUBSTRING(@str, @MAXLEN + 1, 1);
			IF (@chr = ' ')  -- Check if the character at position @MAXLEN is a space or not
				SET @newStr = SUBSTRING(@str, 1, @MAXLEN); -- Sub-string to @MAXLEN
			ELSE -- Not a space (in the middle of a word)
				BEGIN
					SET @tmp = SUBSTRING(@str, 1, @MAXLEN);
					SET @index = CHARINDEX (' ' ,REVERSE(@tmp)); -- Get the reverse index of the first space from @tmp string
					IF (@index > 0) -- Found a space
						SET @newStr =  RTRIM(SUBSTRING(@tmp, 1, @MAXLEN - @index));  -- Sub-string to the last space before @MAXLEN position then right trim
					ELSE -- Not find a space but have to split to @MAXLEN any way (in reality this probably never happen)
						SET @newStr = SUBSTRING(@str, 1, @MAXLEN); -- Sub-string to @MAXLEN
				END
			print @newStr;
			SET @str = LTRIM(SUBSTRING(@str, LEN(@newStr) + 1, LEN(@str) - LEN(@newStr)));
		END
		print @str;
	END
ELSE -- Not longer than 132 characters
	print @str;