debanikray avatar image
debanikray asked

Need help with Splitting Column(ntext) to Rows with column of length 255 and pattern matching

We have a scenario with the data as


1 | FDR-1234-12345,FDR-12345-123456,FDR-0000-0000

2 | FDR-3456-3456,FDR-23456-23456,FDR-45678-45678,FDR-1234-12345

The FDRT Column is of datatype ntext. What we intend to achieve is split the FDRT column into max 255 character and transpose based on ID. When we do the split each FDRT value should come as a valid value. The FDRT values are of variable length and comma separated. So if a line goes to 257 and hence is chopping of a bit of any FDRT value then move the last FDRT in this Column to next row.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered

This answer to a similar question refers to 7 very good resources on splitting a delimited string using sql -

One you have your values split into rows, it’s just a matter of joining any FDRT leading fragments with their trailing fragments in the next row (I would suggest a split approach that allows for row numbering to facilitate the fragment matching)

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@KenJ Too bad that the scripts in the answers lost their formatting after this site was upgraded to its current UI. I wish there was a way to roll it back to the previous version because this one is terrible, i.e. many old answers are rubbished by the means of stripped formatting, there are no buttons to upvote/downvote, the dreaded "like" link does not make any sense because it does not propagate the count of them "likes" to the main page. For example, I "liked" your answer, but if I go back to the main page, the text on the right side reads "2 replies, 0 likes". Why is it 0 when there are upvotes?

@debanikray You have not mentioned the version of the SQL Server you have, but if it is 2016 or newer you can also use string_split, though you will need to cast the FDRT as nvarchar(max) because the function cannot work with ntext. ntext is being deprecated and should never be used. It is easy enough to use varchar(max) or nvarchar(max) data types instead,

0 Likes 0 ·

@Oleg, I didn't realize that the old answers had their formatting removed - I do recall noting it in passing on the meta side. That definitely breaks pretty much all the previous split answers!


In short, if you are running a version of SQL older than 2016, you can use this method (sample code download at the end of the article) -

If you are running SQL 2016 or newer, you can use the built-in string_split -

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.