Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    5

    Unanswered: Report part of a link

    I am attempting to report parts of a field which is made up of several URL links.
    The text is entered in the database as VARCHAR(8000), and when I report on that field I get something similar to below (see ****)

    What I want see is just the number character parts following by title,
    i.e.
    6.8.11.5 TestCase
    6.8.11.5 TestCase2
    6.8.3.11 TestFour
    6.8.6.11 TestFive

    I am not sure if it is a Format change I require in the report, or SQL to
    extract the parts I need to see.


    ****
    <A target="_new" title="6.8.11.5. TestCase (SLMNI)"
    href="http://www.testnet.org.uk/s/index/categories/category_test/category_endi
    ng_test/category_testtwo/06081205_ewsni_testtwo.htm">6.8.11.5.
    TetCase2 (EWSNI)</A><BR><A target="_new" title="6.8.3.11. TestFour (WES)"
    href="http://www.testnet.org.uk/s/index/categories/category_test/category_endi
    ng_test/category_under/06080611_testdat.htm">6.8.6.11. TestFive (WES)</A><BR><BR>

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So for that one row of source data you want the four rows in the result set? If so I think a CLR table function using regex to parse the string is your best bet.

  3. #3
    Join Date
    Jul 2009
    Posts
    5

    How to extract parts of a URL address?

    Hi,

    Thank you for your reply, but being relatively new to SQL, that hasn't got me to where I need to be.
    I am also using t-sql, SQL Server 2005, and Reporting Services.

    I guess though I need to use sql to select parts of the string, which I can find how to do if it is a date or a time part of a string, but I can't figure out the SQL to extract the parts of the address I need to show in a report.

    Thanks,

    Gillian

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Return the whole string to your reporting tool and dissect it there. You can dissect the string using SQL Server, but that makes you solution needlessly complex.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't know how to do this with reporting services - do you Pat?
    I do know how to do this with SQL, but that is not the best language for this.
    I do know how to do this with managed code perhaps as CLR (this is sort of like integrating a DLL written in a .NET language into SQL Server). It is though reasonably involved guiding you from a standing start.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sure, bring the contents of the column back to reporting services, then write a function within the Reporting Services framework that uses reg-exp to parse the URL into a form your report can easily consume/use.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jul 2009
    Posts
    5

    Help?!

    The whole string is repeating links to web pages.
    I've searched around and although I understand the concept of regex, I am not experienced enough to have a clue how to start writing the code to split up the addresses to then only show part of it in the report, whether it is done at the report tool side or not.
    I need a really simple dummies guide on regex, but everything I find looks like piles of complicated code I can't get my head around.
    I can kind of read it, but at a loss as to how to start writing it for myself.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •