[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

Parse a URL in SQL, and match result from a DB

I have a web service that generates different content based on the URL of the client accessing the web service.  This works well...except I've been told to 'move' my business logic to the SQL server, to minimize the network traffic.

My current implementation takes the URL http://first.second.com/one/two/three/four.htm and splits the string into an array based on the '/' delimiter.  Then, starting at the back, I test the file name to see if it matches for 'virtual' page creation (ie. if it's contact.htm, I generate a contact page for the requesting site out of the current phone book...ensuring all contact info is current and up to date).  If the file name doesn't match, then I go to the next part of the URL (/three/ in this instance), and do a DB query to see if it exists there.  If it does, then I return all of the information in the database, and generate the custom page for the user.  If the DB does not have an entry, then I iterate up to the next level (/two/) to see whether that exists in the database.

Rinse and repeat for the entire length of the presented URL.

As I said, I have this working now, with a rather nice recursive method built into the web service...but I've been told NOT to make repeated network calls from the web service to the database.  I have another problem too...in that users may well have multiple /three/ sub folders, (ie. /one/three/four.htm, /two/three/four.htm, /four/three/four.htm may ALL exist...and require different content...based on the root folder) but I think I have an approach to deal with that.

What I'm asking / requesting, is a stored procedure that will take a URL (ie. http://first.second.com/one/two/three/four.html) and will match up to the correct folder part (ie. /three/ or /two/), to determine what information to provide back to the web service.

tblContent
------------
urlPart varchar(10) - ie. one, two, three, etc. (potentially one/two, two/three)
content varchar(50) - information to return to the web service based on the requesting URL part

Ideally, my web service would call "sp_matchURL(@URL)" and retrieve a dataset with the content that matches based on the first found instance of the url part.

Help!  **laugh**  As I said, it's frustrating to me to HAVE this solution...and have to redo it all on the SQL server.  Thanks in advance to all the experts who take the time to assist...

Shaymus
0
ShaymusBane2
Asked:
ShaymusBane2
  • 3
  • 2
1 Solution
 
Anthony PerkinsCommented:
Quite frankly SQL Server (or more appropriately T-SQL) is not designed for parsing strings and your best bet is to pass SQL Server all the values as separate items.  Having said that, this is just a simple case of a delimited string.  Here is one solution that will take the URL and convert to a table of items.

Declare @URL varchar(1000) ; Set @URL = 'http://first.second.com/one/two/three/four.html'           -- For testing only


Declare @Temp table(
      ID integer IDENTITY(1, 1),
      Item varchar(50))

Declare @Pos smallint

SET NOCOUNT ON
Set @URL = REPLACE(@URL,'http://', '')      -- Remove any leading http://
Set @URL = REPLACE(@URL, '.html', '.')      -- Remove any trailing html
Set @URL = SUBSTRING(@URL, CHARINDEX('/', @URL) + 1, LEN(@URL)) -- Remove the domain
Set @URL = REPLACE(@URL, '/', '.')       -- This should make it easier to parse

Set @Pos = CHARINDEX('.', @URL)

While @Pos > 0
   Begin
      Insert @Temp(Item) Values (LEFT(@URL, @Pos - 1))
      Set @URL = SUBSTRING(@URL, @Pos + 1, LEN(@URL))
      Set @Pos = CHARINDEX('.', @URL)
   End

Select Item
From @Temp
Order By ID Desc

This produces:
Item
four
three
two
one

0
 
ShaymusBane2Author Commented:
Sorry for the slow reply, but thank you for the prompt answer!  I only work Mon-Fri, and wasn't able to set up to test this until this morning.

This looks precisely like what I'm needing to do though!  Full points awarded.

One thing though...after retrieving the table of elements, would it be a matter of another iteration through that list, querying the existing table to find the match?  Then do a count condition to determine when you in fact do find your matching row entry?

You spoke of this as not being the 'best bet'...is that due to performance of SQL for parsing of the data?  As I said, I'd prefer to keep things as they are...and if there is a genuine advantage of doing everything within the existing web service rather than on the SQL server, I'd love to float that information back...and potentially relieve myself from having to rewrite all of my existing code  **grin**
0
 
Anthony PerkinsCommented:
>> would it be a matter of another iteration through that list, querying the existing table to find the match? <<
It is difficult to know for sure, as I may not be following your logic entirely.  My understanding was that you wanted a list of results sorted from last to first, so that you can check each value, that is that that solution provides you.

>>is that due to performance of SQL for parsing of the data?<<
Right, T-SQL is not very fast at doing string manipulation.  It can do it, it is just not very efficient.

>>and if there is a genuine advantage of doing everything within the existing web service rather than on the SQL server<<
Languages such as C++, VB, Java, .NET are far more efficient at doing this.

P.S. "I've been told to 'move' my business logic to the SQL server, to minimize the network traffic."
Quite frankly, I don't believe this is very sound advice and whoever stated it not very experienced.
0
 
ShaymusBane2Author Commented:
Thank you again for the response...most importantly for the statement that SQL is NOT the best way to do this!  **grin**  Judging by the number of expert points you have on the side panel, you know something about this subject area.

First part...After I've parsed the URL, I need to produce specific content back to the user.  If they came in from /three, then they will get content specific for that directory.  It gets more complex though, as they could have arrived from /two/three OR /one/three, or any other alternatives...and the content for /two/three will be different than the content for /one/three....when I spoke of re-iterating through, what I was proposing my next step in SQL would have to be, would be to take the newly created table, and do my original query using the specifc parameters in the new table for my WHERE clause.  ie.

SELECT content FROM tblContent WHERE (SELECT item FROM @temp WHERE id=x)

Then iterate THAT select statement around until you receive a match...once again, I have this logic fleshed out in the web service, and it works...so if'n you're saying SQL is not the best (read: most efficient) method to do this...I'll go back to using my complete web service :)

I've been discussing this situation with our network guy, and it appears we ARE somewhat limited with regards to how much of a data footprint we serve out to the clients...but since the IIS and SQL servers are doing all the communicating, and they are all on fiber in the same room, the network traffic there is minimal...the client performs one request, and gets one response...so for the client, the data footprint is already small...doing the SQL parsing doesn't create any 'advantage'.

Once again, thank you very much for the responses.

Shaymus
0
 
Anthony PerkinsCommented:
>>Judging by the number of expert points you have on the side panel, you know something about this subject area.<<
Don't be mislead by that.  I just happen to have been here longer than most and have the advantage of not having a life :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now