Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84
If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:

>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84
|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!
|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:

>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!