SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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)
Code

And the output of the table-valued split function udf_SplitIntoFixedLengthString with above parameters is as seen in below screenshot.

split string into fixed length pieces using SQL function
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)
Code

And this is the returned table of the split string function which has every character in a seperate table row in order.

split string into its characters using SQL
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



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.