Split String Into Fixed Length Pieces in SQL
Although to split a string into fixed length parts is not a suitable task for SQL in SQL Server, developers frequently require codes to split string with different requirements. For example split string using a delimeter can be a requirement as well as splitting string variable into fixed length pieces.
This SQL tutorial will provide a user defined SQL function which splits given input string in desired length pieces and returns within a table structure. SQL split string function is created as a table-valued function which returns a table structure with lines populated with string parts.
I've copied the sql code sources of the sample user defined function below. As developers will recognize at once, the split function takes two parameters. First of these two input arguments, is the string itself which is expected to be returned in fixed length pieces. The second argument is the length of the maximum string pieces.
SQL split string function takes substring of the input argument starting from the first character in the given length.
When first string piece is read, the second piece is read using the same length in a SQL WHILE loop.
Then this splitted string pieces are stored in the return table variable.
create function dbo.udf_SplitIntoFixedLengthString (
@string nvarchar(max),
@stringlength int
) returns @list table (
string nvarchar(max)
)
as
begin
-- sql function begins
if len(@string) > 0 and @stringlength > 0
begin
declare @i int -- character index
set @i = 1
while @i <= len(@string)
begin
insert into @list (string) select SUBSTRING(@string,@i,@stringlength)
set @i = @i + @stringlength
end
end
return
-- sql function ends
end
When all input string characters are processed using the WHILE loop, the return table is populated with all available string pieces. Using the Return command the populated table is returned back where the table-valued function is first called.
Let's make the use of this SQL split function more visual with a sample.
Following SQL code executes SQL split string function udf_SplitIntoFixedLengthString and passes a string variable in nvarchar() data type. Second parameter 30 sets fixed length of each splitted string, the last string part will possibly less than the given fixed length.
declare @string nvarchar(max)
set @string = N'This SQL tutorial shows how to split a string variable into fixed length sub-string values'
select * from dbo.udf_SplitIntoFixedLengthString(@string, 30)
And the output of the table-valued split function udf_SplitIntoFixedLengthString with above parameters is as seen in below screenshot.
Splitting string variable into fixed length pieces using SQL function udf_SplitIntoFixedLengthString
If developers provide the string legth parameter as 1, of course the split string sql function will return each character of the given string variable as a different row.
So the string will be splitted into its characters.
Here is an other SQL example function call.
select * from dbo.udf_SplitIntoFixedLengthString(N'SQL split string function', 1)
And this is the returned table of the split string function which has every character in a seperate table row in order.
Using udf_SplitIntoFixedLengthString with fixed length parameter equal to 1, a string can be splitted into its characters
As an alternative to last usage of the given SQL split function, database developers can use SQL Character Split Function, too