SQL REPLACE() function

SQL allows you to simply replace parts of a string by using REPLACE(fieldname, „replace-this“, „replace-with-this“) and yes you can encapsulate REPLACE() within other REPLACE(). Thats easy to use and efficient because you don’t need to order and sort your results in PHP or any other language, but directly get the correct format and order of your data.

Our table:

mysql> SELECT * FROM site_logs;
+----+-----------------------+---------------------+
| id | url                   | data                |
+----+-----------------------+---------------------+
|  1 | https://www.google.de | 2016-11-09 18:13:01 |
|  2 | https://www.google.de | 0000-00-00 00:00:00 |
|  3 | http://www.bing.com   | 2016-11-09 18:13:01 |
|  4 | http://www.bing.com   | 0000-00-00 00:00:00 |
|  6 | https://www.bing.com  | 0000-00-00 00:00:00 |
|  7 | http://www.google.com | 0000-00-00 00:00:00 |
|  8 | https://www.yahoo.com | 2016-11-09 18:13:01 |
|  9 | https://www.yahoo.com | 0000-00-00 00:00:00 |
+----+-----------------------+---------------------+

Our easy grouped Table:

mysql> SELECT * FROM site_logs GROUP BY url;
+----+-----------------------+---------------------+
| id | url                   | data                |
+----+-----------------------+---------------------+
|  3 | http://www.bing.com   | 2016-11-09 18:13:01 |
|  7 | http://www.google.com | 0000-00-00 00:00:00 |
|  6 | https://www.bing.com  | 0000-00-00 00:00:00 |
|  1 | https://www.google.de | 2016-11-09 18:13:01 |
|  8 | https://www.yahoo.com | 2016-11-09 18:13:01 |
+----+-----------------------+---------------------+

Our table with the use of REPLACE():

mysql> SELECT * FROM site_logs GROUP BY REPLACE(REPLACE(url, "https://", ""), "http://", "");
+----+-----------------------+---------------------+
| id | url                   | data                |
+----+-----------------------+---------------------+
|  3 | http://www.bing.com   | 2016-11-09 18:13:01 |
|  7 | http://www.google.com | 0000-00-00 00:00:00 |
|  1 | https://www.google.de | 2016-11-09 18:13:01 |
|  8 | https://www.yahoo.com | 2016-11-09 18:13:01 |
+----+-----------------------+---------------------+

An even better output:

mysql> SELECT id, data, REPLACE(REPLACE(url, "https://", ""), "http://", "") AS site FROM site_logs GROUP BY REPLACE(REPLACE(url, "https://", ""), "http://", "");
+----+---------------------+----------------+
| id | data                | site           |
+----+---------------------+----------------+
|  3 | 2016-11-09 18:13:01 | www.bing.com   |
|  7 | 0000-00-00 00:00:00 | www.google.com |
|  1 | 2016-11-09 18:13:01 | www.google.de  |
|  8 | 2016-11-09 18:13:01 | www.yahoo.com  |
+----+---------------------+----------------+

And of course we need to order this:

mysql> SELECT id, data, REPLACE(REPLACE(url, "https://", ""), "http://", "") AS site FROM site_logs GROUP BY REPLACE(REPLACE(url, "https://", ""), "http://", "") ORDER BY id ASC;
+----+---------------------+----------------+
| id | data                | site           |
+----+---------------------+----------------+
|  1 | 2016-11-09 18:13:01 | www.google.de  |
|  3 | 2016-11-09 18:13:01 | www.bing.com   |
|  7 | 0000-00-00 00:00:00 | www.google.com |
|  8 | 2016-11-09 18:13:01 | www.yahoo.com  |
+----+---------------------+----------------+

Easy, fast and much better than using a second language to work with our SQL result.