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.