CODEWITHSUNDEEP
sqlsql-serversql-order-byconcatenation
user1977591
user1977591

Reputation: 211

How can I order a query by the domains of email addresses?

I have not used concatenation in SQL much and don't know how I would do this.

Say I have an Email column containing a few email addresses (varchar). Then I have another column for LastLogin (datetime).

[email protected]
[email protected]
[email protected]

How would a query look for sorting firstly by the lastLogin date, and then by the domain name of the emails?

Upvotes: 1

Views: 1325

Answers (3)

fr33g
fr33g

Reputation: 2279

Assuming you're using MySQL, the following query should work for you:

SELECT * FROM YourTable ORDER BY lastLogin, SUBSTRING_INDEX(email, '@', -1);

You provide to SUBSTRING_INDEX the name of the column, your delimiter and the number of occurrences of the delimiter as -1, in order to get everything on the left hand side after the delimiter.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use charindex() and substring():

order by lastLogin,
         substring(email, charindex('@', email) + 1, len(email))

If you want the date component of the login:

order by cast(lastLogin as date),
         substring(email, charindex('@', email) + 1, len(email))

Upvotes: 2

Ajit Kumar
Ajit Kumar

Reputation: 345

Try this may help

select * from table order by  `lastLogin date`,reverse(email);

Upvotes: 0

Related Questions