Display More Than 8000 Characters (SQL Spackle)

  • Jeff Moden

    SSC Guru

    Points: 994663

    Comments posted to this topic are about the item Display More Than 8000 Characters (SQL Spackle)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • ColdCoffee

    SSC-Dedicated

    Points: 39971

    Thanks for this cute Spackle,Jeff 🙂

  • samir-424465

    SSC Veteran

    Points: 259

    We can use BCP utility to get the data of the table (queryout option). The contents of the file will have character field (with varchar(MAX) / nvarchar(MAX)) with all the data in it.

  • chuckh 3191

    SSC Rookie

    Points: 40

    You don't have to have a Tally table made in your database. Using common table expressions, you can generate them on the fly. Here is such using the example code from the article where we reaplace Tally with cteTally

    --===== Declare a couple of long string variables of two different datatypes

    DECLARE @LongString VARCHAR(MAX),

    @NLongString NVARCHAR(MAX)

    ;

    --===== Fill each string with 10,000 GUIDs followed by a space

    -- for a total of 369999 (+1 trailing space) characters.

    ;WITH

    cteTally AS

    (--==== Create a Tally CTE from 1 to whatever the length

    -- of the parameter is

    SELECT TOP 20000

    ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N

    FROM Master.sys.All_Columns t1

    CROSS JOIN Master.sys.All_Columns t2

    )

    SELECT @LongString = (SELECT CAST(NEWID() AS CHAR(36)) + ' '

    FROM cteTally t

    WHERE t.N BETWEEN 1 AND 10000

    FOR XML PATH('')),

    @NLongString = @LongString

    ;

    --===== Just confirming the length of the strings here

    SELECT LEN(@LongString), LEN(@NLongString)

    ;

    --===== Let's solve the problem with a little control over the width

    -- of the returned data. This could easily be converted into

    -- an inline Table Valued Function.

    DECLARE @Width INT;

    SELECT @Width = 8000;

    --===== Show that the solution works on VARCHAR(MAX)

    ;WITH

    cteTally AS

    (--==== Create a Tally CTE from 1 to whatever the length

    -- of the parameter is

    SELECT TOP (LEN(@LongString))

    ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N

    FROM Master.sys.All_Columns t1

    CROSS JOIN Master.sys.All_Columns t2

    )

    SELECT StartPosition = (t.N-1)*@Width+1,

    SliceData = SUBSTRING(@LongString,(t.N-1)*@Width+1,@Width)

    FROM cteTally t

    WHERE t.N BETWEEN 1 AND LEN(@LongString)/@Width+1

    ;

  • Koen Verbeeck

    SSC Guru

    Points: 258940

    Thanks, great article Jeff.

    You find uses for the Tally table everywhere, don't you 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • nigel.

    SSChampion

    Points: 11579

    Thanks Jeff, nice article.

    Spotted a minor typo in the first code example, has SliceDate instead of SliceData for the column alias.

    I know it has no real effect. But, knowing what a perfectionist you are .:-)

  • Matt Whitfield

    SSCrazy Eights

    Points: 8107

    Good article Jeff, nice one.

    It does surprise me, though, that it's even necessary. I mean, it's 2011...

    I guess that sort of thing is why I very rarely use SSMS... 🙂

    Atlantis Interactive - SQL Server Tools
    My blog[/url]
    Why I wrote a sql query analyzer clone

  • dave.clark

    SSC Veteran

    Points: 235

    Thanks for the excellent info!

    I tackled this issue myself previously, but my solution is not quite as "simple" 😉

    I'll have to test this and see if I can replace my lengthy solution.

  • thisisfutile

    Hall of Fame

    Points: 3484

    nigel. (1/27/2011)


    Thanks Jeff, nice article.

    Spotted a minor typo in the first code example, has SliceDate instead of SliceData for the column alias.

    I know it has no real effect. But, knowing what a perfectionist you are .:-)

    I noticed that too. Is it wrong for me to find some sort of comfort in knowing that even Jeff Moden makes the date/data typo? 😛

    If I had a dollar for every time I've done that...

    Thanks for the article Jeff. I always enjoy seeing how you solve "the loop" issue.

  • nigel.

    SSChampion

    Points: 11579

    thisisfutile (1/27/2011)


    nigel. (1/27/2011)


    Thanks Jeff, nice article.

    Spotted a minor typo in the first code example, has SliceDate instead of SliceData for the column alias.

    I know it has no real effect. But, knowing what a perfectionist you are .:-)

    ... Is it wrong for me to find some sort of comfort in knowing that even Jeff Moden makes the date/data typo? 😛

    No, definitely not 🙂

  • Ron McCullough

    SSC Guru

    Points: 63877

    Jeff thanks again, going to be adding it to my tool box. Now wondering if that tool box should be renamed as "ToolboxbyJeffModen"

    Yes I know the articles are labeled as "Spackle", but heck with your articles I not only can fill in the cracks, but build an entire wall.

    Again thanks.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • nadabadan

    Default port

    Points: 1493

    "t.N BETWEEN 1 AND LEN(@LongString)/@length+1" would list an extra blank row where LEN(@LongString) is a multiple of @length.

    DECLARE @longString varchar(max)

    DECLARE @val varchar(max)

    SET @val = 'abcdefgh'

    SELECT @longString = REPLICATE( @val, 10000 ) -- 80000 char length string

    If you use the string above to split the string into 8000 char strings, you will get 11 rows instead of the expect 10.

    Perhaps

    WHERE N <= CEILING( LEN( @longString ) / CONVERT( numeric(10,1 ), @length ) )

    OR

    WHERE t.N BETWEEN 1 AND CEILING( LEN( @longString ) / CONVERT( numeric(10,1 ), @length ) )

    would be better.

  • thisisfutile

    Hall of Fame

    Points: 3484

    nadabadan (1/27/2011)

    ...you will get 11 rows instead of the expect 10.

    In the article, Jeff stated "I don't really care where the line "wraps", nor do I care about trailing spaces". I'm not trying to speak for Jeff (or am I?), but to me an extra row sort of fits into this area that has been purposely ignored. Cosmetics aren't necessarily important for this spackling.

    IMHO

    A good solution, nonetheless.

  • Jeff Moden

    SSC Guru

    Points: 994663

    ColdCoffee (1/26/2011)


    Thanks for this cute Spackle,Jeff 🙂

    You bet, CC. Thanks for stopping by.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994663

    samir-424465 (1/26/2011)


    We can use BCP utility to get the data of the table (queryout option). The contents of the file will have character field (with varchar(MAX) / nvarchar(MAX)) with all the data in it.

    Hi Samir,

    Thanks for stopping by with the tip. I absolutely agree with the above and there are many other methods you can use, as well. That also takes a bit more time than what I need to take when I'm just trying to do a simple check while writing code in SSMS. It can also require elevated privs and not everyone has the privs to run BCP nor write to disk from an SQL Server.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 68 total)

You must be logged in to reply to this topic. Login to reply