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

Question

question

debanikray asked Nov 27, '18

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

We have a scenario with the data as

ID | FDRT

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.

column split comma

10 |1200

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

Answer 1 · 2018-11-28T00:33:39Z

KenJ answered Nov 28, '18

This answer to a similar question refers to 7 very good resources on splitting a delimited string using sql - https://ask.sqlservercentral.com/answers/122184/view.html

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)

2

10 |1200

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

Oleg commented · Nov 28, 2018 at 02:14 PM

@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 ·

KenJ Oleg commented · Nov 28, 2018 at 07:31 PM

@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!

@debanikray,

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) - http://www.sqlservercentral.com/articles/Tally+Table/72993/

If you are running SQL 2016 or newer, you can use the built-in string_split - https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

0 ·

question