Post reply

Building a Comma Separated on a new line

  • October 3, 2013 at 8:53 pm

    #277757

    Hi All,

    I need some help! firstly to tweak my results to work as I expect it to & secondly provide an alternate method if my logic is not best practice.

    I'm returning a list of PDF's (very minimal results < 20) into a temporary table and then running the following query to build these into a string. (E.G. 1.pdf,2.pdf,3.pdf ... 20.pdf)

    DECLARE @PDFFileName VARCHAR(8000) =

    (

    SELECT STUFF(( SELECT ', ' + PDF_FileName + '.pdf'

    FROM @PDFResult

    ORDER BY 1 ASC FOR XML PATH('')),1,1,'')

    )

    My aim is to split these up 1 per line instead of a massive line (as below)

    1.pdf,

    2.pdf

    ...

    20.pdf

    Any ideas?

  • October 3, 2013 at 9:04 pm

    #1655729

    forgot to add some important details. reason I'm doing it this way is because I'm passing the variable @PDFFileName into a email template using:

    REPLACE(REPLACE(HTML,'[[FileName]]',@ProcessingFile),'[[PDF_FileName]]',@PDFFileName)

  • October 4, 2013 at 2:00 am

    #1655770

    Tava (10/3/2013)

    Hi All,

    I need some help! firstly to tweak my results to work as I expect it to & secondly provide an alternate method if my logic is not best practice.

    I'm returning a list of PDF's (very minimal results < 20) into a temporary table and then running the following query to build these into a string. (E.G. 1.pdf,2.pdf,3.pdf ... 20.pdf)

    DECLARE @PDFFileName VARCHAR(8000) =

    (

    SELECT STUFF(( SELECT ', ' + PDF_FileName + '.pdf'

    FROM @PDFResult

    ORDER BY 1 ASC FOR XML PATH('')),1,1,'')

    )

    My aim is to split these up 1 per line instead of a massive line (as below)

    1.pdf,

    2.pdf

    ...

    20.pdf

    Any ideas?

    Try this:

    DECLARE @PDFFileName VARCHAR(8000) =

    (

    SELECT STUFF(( SELECT ', ' + PDF_FileName + '.pdf' + char(13) + char(10)

    FROM @PDFResult

    ORDER BY 1 ASC FOR XML PATH('')),1,1,'')

    )

  • October 7, 2013 at 4:21 pm

    #1656562

    I read about using the CHAR(13) + CHAR(10) and i tried that but no difference.... I then read to just use CHAR(10) and that also failed to work.

    There were no errors, just didnt work as expected.

  • October 7, 2013 at 4:51 pm

    #1656569

    Where are you checking the results? The grid from SSMS won't show line feeds.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 8, 2013 at 7:01 am

    #1656711

    Try this

    DECLARE @PDFFileName VARCHAR(8000) =

    (

    SELECT STUFF(( SELECT ', ' + PDF_FileName + '.pdf' + char(13) + char(10)

    FROM @PDFResult

    ORDER BY 1 ASC FOR XML PATH(''),type).value('(./text())[1]','Varchar(8000)'),1,1,'')

    )

    Select @PDFFileName

  • October 8, 2013 at 3:55 pm

    #1656935

    Luis Cazares (10/7/2013)

    Where are you checking the results? The grid from SSMS won't show line feeds.

    I'm checking the results in the actual out of the email, not the grid in SSMS.

  • October 8, 2013 at 4:00 pm

    #1656938

    It sounds to me like your email may have an html body, in which case, replace the char(13), char(10) combination with a '< br / >' (remove the spaces) tag, which has the same effect on an html page.

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • October 8, 2013 at 4:11 pm

    #1656942

    Edward Boyle-478467 (10/8/2013)

    Try this

    DECLARE @PDFFileName VARCHAR(8000) =

    (

    SELECT STUFF(( SELECT ', ' + PDF_FileName + '.pdf' + char(13) + char(10)

    FROM @PDFResult

    ORDER BY 1 ASC FOR XML PATH(''),type).value('(./text())[1]','Varchar(8000)'),1,1,'')

    )

    Select @PDFFileName

    hmmm, it didn't work still displays in my email as a long concatenated string " a.pdf,b.pdf,c.pdf "

    I'm not too fussed about getting it working as it doesn't look too bad but now I'm just curious for a solution.

  • October 8, 2013 at 4:16 pm

    #1656944

    mister.magoo (10/8/2013)

    It sounds to me like your email may have an html body, in which case, replace the char(13), char(10) combination with a '< br / >' (remove the spaces) tag, which has the same effect on an html page.

    Yeah it is HTML body, I've tried using the br or p syntax and no difference.

  • October 8, 2013 at 4:18 pm

    #1656945

    I can only think there was a problem with the way you tried?

    Perhaps if you generate some sample html and post it here we can see what is wrong?

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • October 8, 2013 at 4:26 pm

    #1656946

    mister.magoo (10/8/2013)

    I can only think there was a problem with the way you tried?

    Perhaps if you generate some sample html and post it here we can see what is wrong?

    This is my HTML template

    <html>

    <head>

    </head>

    <body>

    <p style="font-family: arial, verdana, times; font-size: 12px; color: #000000;">Original Zip Filename.<br/><br/>[[FileName]]<br/><br/>Missing PDF File.<br/><br/>[[PDF_FileName]]

    </p>

    </body>

    </html>

    This is my HTML Code, [[PDF_FileName]] is replaced with the string...

  • October 8, 2013 at 4:31 pm

    #1656950

    by putting the '< br/ >' (spaces were removed) it generates this

    <html><head></head><body><p style="font-family: arial, verdana, times; font-size: 12px; color: #000000;">Original File.<br/><br/>test.zip<br/><br/>PDF Filename.<br/><br/> abc123.pdf<br/>, test.pdf<br/>, test123.pdf<<br/></p></body></html>

    when i execute it i can see now its got weird characters " & lt ; br / & gt ; "

    edit: had to put spaces in otherwise it was correctly showing br br

  • October 8, 2013 at 4:33 pm

    #1656951

    When you replace [[PDF_FileName]] does the rendered html have <br/> between each file name?

    Can we see the final html (redacted)?

    edit:

    I see you have posted further ...

    Get the html right and it will work 🙂

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • October 8, 2013 at 4:37 pm

    #1656953

    sorry, IE crashed as i edited the reply 🙂

    ummm, when i select my variable i get the wierd characters etc.... but when the email generates it shows BR.

    how can i pass through BR if it keeps getting translated?

    • Viewing 15 posts - 1 through 15 (of 19 total)

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

    Cookies on SQLServerCentral

    This website stores cookies on your computer.

    These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

    To find out more about the cookies we use, see our Privacy Policy