I am using C# and ASP.NET 2.0, with SQL Server 2000. In my database I have a table that is similar to the following:
WebpageId WebpageAddress Handler
1 /company/about ~/about.aspx
2 /blog ~/blog.aspx
As you can guess, one of my queries will be a SELECT command where WebpageAddress = @.address.
The hiccup I have is with a friendly URL such as the following:
/blog/2005/10/6
The friendly URLs have no extension, so I cannot immediately "pick out" the extension. (If the address were /blog.aspx/2005/10/6, then things would be simpler.)
What I am doing at present is using a loop in C# where I first perform a query with that full address. If no match is found, I trim the address back to the last slash (/blog/2005/10) and perform another query. If no match is found, I trim the address (/blog/2005) and perform another query. Again, no match is found, so I trim the address again (/blog) and perform yet another query. This time, a match is found, in which case the URL rewriting looks vaguely like this:
~/blog.aspx?parameters=2005/10/6
While this works fine, these friendly URLs require hitting the database up to four or five times. My question is, can this looping logic be moved to SQL Server?
My SQL knowledge is extremely basic, so I am just looking for someone to point me in the right direction. If it is not possible, I'd love to know now rather than wasting hours trying. If it is possible, I've love to know the keyword or technique involved, so that I can Google for the full answer.
SELECT TOP 1 Handler
FROM MyTable
WHERE WebpageAddress=SUBSTRING(@.address,1,LEN(WebpageAddress))
ORDER BY LEN(WebpageAddress) DESC
I think will give you what you want. However, to answer your question, yes, you can loop as well, but it'd be slower.
