Select question

Hi there,

I have a database where I have many web page addresses. I need to select each (unique) domain name, not page.

For instance, I have like this:

http://mydomain.com/page1
 
http://mydomain.com/page2
 
http://otherdomain.com/page1 
 
http://someotherdomain.com/page1 

Code (markup):

I need to select unique domain names, so the result should be:

http://mydomain.com
 
http://otherdomain.com 
 
http://someotherdomain.com

Code (markup):

How can I achieve this?

Thanks!

 

Hi


you can use SUBSTRING_INDEX function
Syntax :

SUBSTRING_INDEX(str,delim,count)
This syntax will returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.
example :
SELECT SUBSTRING_INDEX('http://www.mydomain.com/page1', '/', 3);
--> result : http://www.mydomain.com

Another solution is use "SUBSTRING" function in SQL that is used to grab a portion of the stored data.

• MySQL: SUBSTR( ), SUBSTRING( )
• Oracle: SUBSTR( )
• SQL Server: SUBSTRING( )

syntax :

SUBSTR(str,pos): Select all characters from <str> starting with position <pos>.
SUBSTR(str,pos,len): Starting with the <pos>th character in string <str> and select the next <len> characters.

The problem is we have different ending position.
So I think you should customize the web page address on the code programming, not on the select query..
The select query will give normal result, then you should substring the result with ending position of character "/". You can use StringTokenizer in Java to find "/".
i hope this solution will help your problem..
thanks

 

If you store the domain in one field and the page in another, SELECT DISTINCT will give you one row for each distinct domain.

 

Rukbat, that's what I did in the meantime. All the other options seemed to be complicated.