CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduresview
N Simpson
N Simpson

Reputation: 67

Split query results with long strings into multiple rows in T-SQL

I have a table in MS SQL Server that contains multiple TEXT fields that can have very long strings (from 0 characters to 100,000+ characters).

I'd like to create a view (or a stored proc that populates a reporting table) that prepares this data for export to Excel, which has a certain character limit allowable per cell (32,767 chars).

It's relatively trivial to write a query to truncate the fields after a certain number of characters, but I'd like to create new rows containing the text that would be truncated.

Example - Row 1, Col1 and Col3 contains text that is wrapped into 2 rows.

ID   |   COL1   |   COL 2   |   COL 3   |
1       AAAAAA     BBBBBBB     CCCCCC
1       AAA                    CC
2       XX         YY          ZZ

Upvotes: 1

Views: 2217

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

You can try something along this:

A mockup table to simulate your issue

DECLARE @tbl TABLE(ID INT IDENTITY, LongString VARCHAR(1000));
INSERT INTO @tbl VALUES('blah')
                      ,('blah blah')  
                      ,('blah bleh blih bloh')
                      ,('blah bleh blih bloh bluuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');

--We can specify the chunk's length

DECLARE @Chunk INT=6;

SELECT t.ID
      ,A.Nmbr AS ChunkNmbr
      ,SUBSTRING(t.LongString,A.Nmbr*@Chunk+1,@Chunk) AS ChunkOfString
FROM @tbl t
CROSS APPLY(SELECT TOP(LEN(t.LongString)/@Chunk + 1) 
                   ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 
            FROM master..spt_values) A(Nmbr);

The idea in short:

We use a trick with APPLY and a computed TOP-clause. The source master..spt_values is just a common table with a lot of rows. We don't need the values, just a set to compute a running number using ROW_NUMBER(). APPLY will be called row-by-row. That means, that a long string will create more numbers than a short one.

To get your chunks I use a simple SUBSTRING(), where we compute the start of each chunk by rather simple multiplication.

UPDATE: More than one column in one go

Try this to use this approach for more than one column

DECLARE @tbl TABLE(ID INT IDENTITY, LongString1 VARCHAR(1000), LongString2 VARCHAR(1000));
INSERT INTO @tbl VALUES('blah','dsfafadafdsafdsafdsafsadfdsafdsafdsf')
                      ,('blah blah','afdsafdsafd')  
                      ,('blah bleh blih bloh','adfdsafdsafdfdsafdsafdafdsaasdfdsafdsafdsafdsafdsafsadfsadfdsafdsafdsafdsafdafdsafdsafadf')
                      ,('blah bleh blih bloh bluuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh','asdfdsaf');

DECLARE @Chunk INT=6;

SELECT t.ID
      ,A.MaxLen
      ,B.Nmbr AS ChunkNmbr
      ,SUBSTRING(t.LongString1,B.Nmbr*@Chunk+1,@Chunk) AS ChunkOfString1
      ,SUBSTRING(t.LongString2,B.Nmbr*@Chunk+1,@Chunk) AS ChunkOfString1
FROM @tbl t
CROSS APPLY(SELECT MAX(strLen) FROM (VALUES(LEN(t.LongString1)),(LEN(t.LongString2))) vals(strLen)) A(MaxLen)
CROSS APPLY(SELECT TOP(A.MaxLen/@Chunk + 1) 
                   ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 
            FROM master..spt_values) B(Nmbr);

The new idea: We use an APPLY first to find the longest string in one row. We have to do the chunk computations only for this maximum number.

Upvotes: 5

Related Questions