Inserting line breaks in column after or before specific character and lenght

Question

Here is another weird data column. This table contains long strings of text. I have been trying to make the column readable and managed to strip most of the rubbish, such as creating a replace function and inserting a colon between specific character but it does not really add to the readability.

Is it possible to break (new line) the string shown below after the last number? I don't know how to do this but it's just an idea. Like say insert line break after X Character of the first Zero, since the numbers starts with zero and are the same length?

This is one of those archive data I stumbled upon.

Here is an example of the table: Note the bottom portion is for dramatization only.

enter image description here

Thanks!

Can you do this in the application (presentation) layer? I guess it should be readable in the application itself, right? — OzrenTkalcecKrznaric, Aug 08 '13 at 05:07
I am thinking worst case I'll just do this by hand or put it in a Dataset and manipulate it in C#, which I know a bit of. — Asynchronous, Aug 08 '13 at 05:11
I think that can be done with a [used defined function](http://technet.microsoft.com/en-us/library/ms186755.aspx), as the standard function set does not allow such an operation. However, I'd do this transformation in the presentation layer, as Ozren suggested — Cristian Lupascu, Aug 08 '13 at 05:16
Did any of the answers solved your problem? If so please mark the question as solved. — Luis LL, Aug 13 '13 at 18:11

score 1 · Answer 1 · edited May 23 '17 at 10:25

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)+6
      UNION ALL
      SELECT pn + 1, stop + 1, 
      CASE 
       WHEN (CHARINDEX(@sep, @s, stop + 1)+6)>LEN(@s) 
       THEN -1 
       ELSE CHARINDEX(@sep, @s, stop + 1)+6
      END
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )

Which can be queried as

  SELECT * FROM dbo.Split ('0', 'Titanic 012345 Casino Royal 012346 Terminator 098987')

Please note that 512 can be changed if you need longer string to be processed. Edit: Solution also assumes there is no '0' in the movie title itself.

Idea taken from Cade Roux who provided link to original solution

score 1 · Answer 2 · answered Aug 08 '13 at 13:16

Based in the excellent approach by @PavelNefyodov an alternative approach using PATINDEX and CROSS APPLY.

This one requires that you know how many digits you are going to have in advance.Fiddle

CREATE  FUNCTION dbo.PatindexSplit (@PatternSearch varchar(100), @PatternLen SMALLINT,@s varchar(max))
RETURNS table
AS
RETURN (

    WITH Pieces(pn, Piece, rest) AS (
      SELECT 1, LEFT(@s, PATINDEX(@PatternSearch, @s)+@PatternLen), SUBSTRING(@s, PATINDEX(@PatternSearch, @s)+@PatternLen+1,LEN(@s))
      UNION ALL
      SELECT pn + 1, 
          CASE WHEN PATINDEX(@PatternSearch, rest) > 0 
            THEN LEFT(rest, PATINDEX(@PatternSearch, rest)+@PatternLen)
            ELSE rest 
            END,
          CASE WHEN PATINDEX(@PatternSearch, rest) > 0 
            THEN SUBSTRING(rest, PATINDEX(@PatternSearch, rest)+@PatternLen+1,LEN(rest))
            ELSE ''
          END 
        FROM Pieces
        WHERE LEN(rest) > 0
    )
    SELECT pn,Piece, rest
    FROM Pieces
)
go 


SELECT userID, Piece FROM Rentals
CROSS APPLY dbo.PatindexSplit ('%0[0-9][0-9][0-9][0-9][0-9] %', 6, MovieTitle)
   SELECT * FROM dbo.PatindexSplit ('%0[0-9][0-9][0-9][0-9][0-9] %', 6, 'Titanic 012345 Casino Royal 012346 Terminator 098987')

Anyway 1+ to do it in the application layer...

+1. Neat solution. And yes it should be implemented in the application layer. — Pavel Nefyodov, Aug 08 '13 at 13:40

Inserting line breaks in column after or before specific character and lenght

2 Answers2