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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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.

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)

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







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums






SQL Split String Related SQL Server Tutorials

Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
Split String Into Fixed Length Pieces in SQL
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
TSQL Character Split Function in SQL Server
Case Sensitive SQL Split Function
SQL Server 2016 Split String Function STRING_SPLIT
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array
T-SQL Split User Defined Function


Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems