Split string into chunks of x-amount of characters


Userlevel 6
Badge +18

Goal

Sometimes you have a very large string that consists of rows of a certain length. Or you have a string you want to split into a certain length of your pleasing. Or you are reading in fixed width files using the Read file connector and the file data is presented to you as a large string.

 

Solution

create function [dbo].[split_string_length]
(
@data_row data_row ,
@length field_length
)
returns @result table
(
sequence id primary key,
result data_row
)
as
begin
--Splits given string into chuncks of a given amount of characters
select @data_row = replace(@data_row, char(13)+char(10), '') --Replace breaklines

declare @sequence int = 1
, @data_length int = datalength(@data_row)
, @row varchar(max)
, @row_left varchar(max)

--While there are still characters to split
while @data_length > 0 and @length > 0
begin
--Put in @s X-amount of characters starting from the left side of the string
select @row = left(@data_row, @length)

--Insert the result
insert @result
values (@sequence, @row)

--Erase x-amount of characters from the left and up the sequence for a unique number
select @data_row = iif(@data_length >= @length --As long as there is more data than the length given
,right(@data_row, @data_length - @length) --Select @length-amount of characters
,null) --Empty
, @data_length = @data_length - @length
, @sequence = @sequence + 1
end
return

end

 

Result

declare @file_data varchar(500)
set @file_data = 'Hello CommunityHello CommunityHello CommunityHello CommunityHello Community'

select *
from dbo.split_string_length(@file_data, 15)
1	Hello Community
2 Hello Community
3 Hello Community
4 Hello Community
5 Hello Community

 


0 replies

Be the first to reply!

Reply