displaying a meaningful name in report instead of a URL name in a field

Question

displaying a meaningful name in report instead of a URL name in a field

ALIF-662928

SSCertifiable

Points: 6054
More actions
May 18, 2009 at 9:45 am

#200559

hi guys,
i have a report, which displays a column of URL, this column gets the URL entry whenever a user accepts a policy, so now i want this URL to be displayed in a meaningful name like ex: http:\\abc\de\ef\policy.aspx to be displayed with a name like 'policy' in the report wherever this URL is there in the database.
i think i have to add another table and put a column for this meaningful name for each URL, but how can i do that,
Can anyone help me out in this, if you need any clarifications let me know,
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

You could do this a couple of different ways. Create a lookup table and join to it in your query as you suggested. This could well be something that gets manipulated in the UI, but you could string out everything between the last \ and the .aspx by using something like the following... I broke it out into steps for clarity. It could easily be combined into a single operation...

DECLARE @myVar AS VARCHAR(100)

SELECT @myVar = 'http:\\abc\de\ef\policy.aspx'

/* Get EVerything after the last '\' */

SELECT @myVar = Right(@MyVar, CHARINDEX('\',REVERSE(@myVar))-1 )

/* Remove Aspx or whatever other extensions might be used... */

SELECT SUBSTRING(@myVar,0,CHARINDEX('.',@myVar))

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 2

Thanks Luke,

i guess this will do for only one entry of such URL, i have multiple URLs in the table, how should i do that for each such.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

I guess what I was trying to say with my example, is that you could still ahve your report as it, with no additional table and just pull out the relevant information in your select statement. I just broke it down into steps to make it easier to read and comprehend. You'd need to put it together using your table names and such and put it into a select statement which feeds your report.

Something If the URL column was named [Url] it might look something like

Select col1, col2, col3, stringmanipulations()

FROM mytable

Obviously stringManipulations is shorthand for what you'd actually need to do to convert the Url to the simple name.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 4

There are ways to get a VB script to go off to the internet with that URL and pull back the page title.

There is a C# method described here:

http://stackoverflow.com/questions/329307/how-to-get-website-title-from-c

Might be a bit too much bother for your requirements though 😛