Hi,

I have a list or URLs in a column (field name URLs).

http://www.domain.com
http://1.domain.com
http://shop.domain.com
shopping.domain.com

etc. I want to bring these urls into an asp list page and order
alphabeticaly. Which means that, where applicable, i need to strip the
http:// and www. out. How can i do this with a query?

Also, if i have a field with a first and surname, how can i return
just the first or second name in a query. I think i need to use
regular expressions but i have never used them before and they look
kinda scary :)

Many thanks

---

Request for Question Clarification by joseleon-ga on 12 Dec 2003 06:17 PST

Hello, apcs_uk:
  In which server are you going to run the query? Access, SQL Server,
MySQL, Oracle... The query can vary depending on this.

Regards.

---

Clarification of Question by apcs_uk-ga on 12 Dec 2003 06:18 PST

MS access 2000

---

Request for Question Clarification by joseleon-ga on 12 Dec 2003 07:24 PST

Hello, apcs_uk:
  I have been looking for info about how to apply regular expressions
in Access queries, but I haven't found anything, maybe another
researcher could help you.

By the way, I think it would be easier and the code will be better if
you process the URLs field in ASP to extract the domain once you have
all the database records, this way will work with any kind of
database.

Regards.

---

Clarification of Question by apcs_uk-ga on 12 Dec 2003 07:26 PST

Hello there,

Can you provide some asp code to accomplish this?

Many thanks

---

Request for Question Clarification by joseleon-ga on 12 Dec 2003 08:26 PST

Hello, apcs_uk:
  Sorry, but one more request. Do you want asp code to strip the
domain from a URL or the full source code that reads the database,
strips the domain and dumps out the results?

Regards.

Hello, apcs_uk:

Here you have a sample ASP scripts that shows you how to extract urls
from a text and then extract all the pieces that make up the URLs,
including the domain name, the part you are interested in:

<%@ Language=VBScript %>
<%
	Option Explicit

	'Declare our variable
	Dim objRegExpr

	'Create an instance of the regexp object
	Set objRegExpr = New regexp


	objRegExpr.Pattern =
"(http:\/\/|https:\/\/|ftp:\/\/|)(([A-Z0-9][A-Z0-9_-]*\.)(([A-Z0-9][A-Z0-9_-]*)(\.[A-Z0-9][A-Z0-9_-]*)))"
	
	objRegExpr.Global = True
	objRegExpr.IgnoreCase = True

	'What string are we searching on?
	Dim strSearchOn
	strSearchOn = "You can searc  http://www.domain.com for URLs in any text " & _
		"string http://1.domain.com/ and http://shop.domain.com/ extract all
that matches the regular shopping.domain.com expression"

	'Declare a variable to hold our collection of Matches
	Dim colMatches

	'Now, Execute the regular expression search
	Set colMatches = objRegExpr.Execute(strSearchOn)

	Dim objMatch

	'Print the # of matches we found
	Response.Write colMatches.Count & " matches found...<P>"

	'Step through our matches
	For Each objMatch in colMatches
		Response.Write "<HR>"		
		Response.Write "Full URL:&nbsp;&nbsp;" & objMatch.Value & "<BR>"
		Response.Write "Protocol:&nbsp;&nbsp;" & objMatch.SubMatches(0) & "<BR>"
		Response.Write "URL w/o domain:&nbsp;&nbsp;" & objMatch.SubMatches(1) & "<BR>"
		Response.Write "First part:&nbsp;&nbsp;" & objMatch.SubMatches(2) & "<BR>"
		Response.Write "Domain name:&nbsp;&nbsp;" & objMatch.SubMatches(3) & "<BR>"
						
	Next

	'Clean up
	Set colMatches = Nothing
	Set objRegExpr = Nothing
%>

Be aware of the word wrapping when copying and pasting.

This script produces the following results:

4 matches found...


--------------------------------------------------------------------------------
Full URL:  http://www.domain.com
Protocol:  http://
URL w/o domain:  www.domain.com
First part:  www.
Domain name:  domain.com

--------------------------------------------------------------------------------
Full URL:  http://1.domain.com
Protocol:  http://
URL w/o domain:  1.domain.com
First part:  1.
Domain name:  domain.com

--------------------------------------------------------------------------------
Full URL:  http://shop.domain.com
Protocol:  http://
URL w/o domain:  shop.domain.com
First part:  shop.
Domain name:  domain.com

--------------------------------------------------------------------------------
Full URL:  shopping.domain.com
Protocol:  
URL w/o domain:  shopping.domain.com
First part:  shopping.
Domain name:  domain.com

I hope this is what you were looking for, don't hesitate to request
for any clarification.

You can get a lot of information about regular expressions applied to
asp in this page:

Regular Expression Information 
http://www.4guysfromrolla.com/webtech/regularexpressions.shtml

Regards.

Fantastic, worked almost straight off. Also very well commented.