Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4AKHqA45028 for ; Thu, 10 May 2001 16:17:52 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 54242 for ; Thu, 10 May 2001 13:17:49 -0700 From: "Josh Berkus" Subject: Re: Order by email address To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Thu, 10 May 2001 13:17:49 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200105/78 X-Sequence-Number: 2879 Herve, > toto@aol.com > tutu@aol.com > toto@be.com > tutu@be.com > toto@yahoo.com > > Is it possible and how ? Given the relational rule of Atomicity (each discrete piece of information shall have its own column or row), the solution is for you to make "e-mail id" and "domain" seperate fields. Then you can sort: ORDER BY mailbox, domain If this is a legacy database, and splitting the field is not an option for you due to exisiting applications/policy, then you'll need to write a custom sorting function: CREATE FUNCTION email_sort (VARCHAR) RETURNS CHAR(120) AS ' DECLARE email_addr ALIAS for $1; mail_box CHAR(60); mail_domain CHAR(60); BEGIN mail_box := CAST(SUBSTR(email_addr, 1, (STRPOS(email_addr, ''@'', 1) -1)) AS CHAR(60)); mail_domain := CAST(SUBSTR(email_addr, (STRPOS(email_addr, ''@'', 1) + 1), 60) AS CHAR(60)); RETURN mail_box || mail_domain; END;' LANGUAGE 'plpgsql'; Then: SELECT user_id, email, email_sort(email) as sortcol FROM users ORDER BY sortcol; However, this solution has a number of problems for data integrity down the line. If e-mail addresses are that important to your application, I greatly encourage you to split the field. -Josh Berkus P.S. Roberto, please add the above to our function library. ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco