Splitting long text columns into multiple NVARCHAR records

Management Studio truncates strings displayed in the result grid which keeps you from viewing the whole string stored in a text column.

A question on Stack Overflow made me create this SELECT statement to split long strings into several records:

SELECT texts.id, 
       SUBSTRING(texts.text_column, number*100+1, 100)
    (SELECT texts.id, texts.text_column, 
        (99+LEN(texts.text_column))/100 AS l
     FROM texts) AS texts
    (SELECT TOP(l) number
     FROM master.dbo.spt_values val
     WHERE name IS NULL
     ORDER BY number) n

In this example, the resulting strings are 100 characters long. The built-in table master.dbo.spt_values contains integer values from 0 to 2047.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: