locked
Adding Hyperlink value in a column by giving another Column value as input RRS feed

  • Question

  • Hello Friends,

    I am trying to create a column that takes another column value from the same source table as input and creates a hyperlink, so that, my clients can simply click on the link and be able to view the details from on internet browser from a different database..

    can I use this as an update queryto acieve the above?

    UPDATE

     

    [CUstomerEncountered BURTS-Case_and_BU_mappings]

    SET

     

    [BURT-LINK] = N'[ http://burtweb.eng.company.com:8080/burt-bin/start?burt-id=[BugNo]'

    (FYI- BugNo - is another column existing in the same table)

    If so, what should be the data type for BURT-LInk column? There seem to be no Hyperlink data type in sqlserver that I could choose from.

    Appreciate your help

    Wednesday, January 19, 2011 9:49 PM

Answers

  • Hi PendekantiS,

     

    Thanks for your post.

    Since this forum is only for SQL Server and this question is more related to Excel, I would like to recommend that you ask it in Excel for Developers forum and you will get specific support.


    Regards,
    Tom Li
    • Marked as answer by Tom Li - MSFT Monday, January 31, 2011 5:48 AM
    Monday, January 24, 2011 7:20 AM

All replies

  • Hyperlink should be stored as character data type (variable length character data type like varchar) so you should be using something like VARCHAR(250)  for storing URL information.

    >>I am trying to create a column that takes another column value from the same source table as input and creates a hyperlink, so that, my clients can simply >click on the link and be able to view the details from on internet browser from a different database

    Ideally, you should be only storing URL (uniform resource locator) in SQL server database, hyperlink with  appopriate HTML tags < a href> </a>  need to be programmed in your client (front end) application.  

    Or appending BugNo as query string paramter must be done in front end application. This logic does not belog in SQL server.

    Wednesday, January 19, 2011 10:11 PM
  • Thanks Chirag, unfortunately I could not update this column properly Is this what you mean by you need to hyperlink with appropriate HTML tags?

    UPDATE [CUstomerEncountered BURTS-Case_and_BU_mappings]

    SET

     

    [BURT-LINK] = < a href> N'(http://burtweb.eng.comapny.com:8080/burt-bin/start?burt-id=[BugNo])' </a>

    This does not work?

    If i give it like below N' a href>(http://burtweb.eng.company.com:8080/burt-bin/start?burt-id=[BugNo])</a '

    then the column is showing something like this...

     a href>(<http://burtweb.eng.company.com:8080/burt-bin/start?burt-id=[BugNo])</a>

    I also do not see it pick up teh value of {BugNo] field here.Any other ideas to make this work?

    • Edited by PendekantiS Thursday, January 20, 2011 6:17 PM Changed company name
    Wednesday, January 19, 2011 10:37 PM
  • something like below

    UPDATE [CUstomerEncountered BURTS-Case_and_BU_mappings]
    SET BURT-LINK] = = '<a href=http://burtweb.eng.netapp.com:8080/burt-bin/start?burt-id=' + CAST(BugNo As varchar(20)) + '/>Hyperlink</a>'
    

     

    or more elaborate example

    declare @t table
    (
    	bugno int
    	,url varchar(200)
    
    )
    insert into @t
    select 2000, 'http://www.yahoo.com?id='
    union all
    select 3000, 'http://www.msn.com?id='
    union all
    select 4000, 'http://www.hotmail.com?id='
    
    update @t
    set url = '<a href="http://www.wsj.com?id=' + CAST(bugno as varchar(20)) + '"/>hyperlink</a>'
    
    select * from @t

    Again, I would rather do this in front end app

    hope it helps

    Thursday, January 20, 2011 3:19 PM
  • Thanks Chirag, The CASt function worked so I can see the actual BugNo for each of it... how ever... when i see the data it looks like this...

    <a href=http://burtweb.eng.company.com:8080/burt-bin/start?burt-id=100146/>Hyperlink</a>(Please note company name changed)

    Iwas expecting something like a URL here.

    Thursday, January 20, 2011 6:19 PM
  • If you are expecting to hyperlinks in SQL Server, that you can click to open a web page,  then I am sorry you cannot do it within SQL server.

    I just show you how to build hyperlink as character data in SQL server, by no means it would present you a link that is available as HTML page hyperlink.

    It appers to me that you are expecting functionliaty that is available in MS Access, unfortunately SQL server is purely designed as a data repository and has no features as front end client application.

    You need to put hyperlink logic in your front end application.

     

    Thursday, January 20, 2011 7:27 PM
  • Ok. I am using excel 2007 as the front end for the report. So there is a way to put this in excel?

    - But, I was actually giving out a pivot... with few filter options to my clients- in that case, they will have to open the counts in order to view all of the details(columns) including this column that has the URL. How can I program this when all i am giving the clients is a pivotted view with count of BURTS

    Thursday, January 20, 2011 7:31 PM
  • Hi PendekantiS,

     

    Thanks for your post.

    Since this forum is only for SQL Server and this question is more related to Excel, I would like to recommend that you ask it in Excel for Developers forum and you will get specific support.


    Regards,
    Tom Li
    • Marked as answer by Tom Li - MSFT Monday, January 31, 2011 5:48 AM
    Monday, January 24, 2011 7:20 AM
  • Hi Tom,

    Thanks for your recommendation. I posted this question in the forum you suggested above and I have not heard even a single response from anyone in the past  24 hours. Also the number of views for the question seem quite less(37) compared what I see in this forum. Any ideas as to how I can ping MS folks to answer my question

    Tuesday, January 25, 2011 6:13 PM