Monday, November 13, 2017

REPLACE function

The replace function is another one of my often used data analysis MySQL functions. However, it can create some ugly queries when it is nested many times. Here is an example I how I used it remove some text from server name. I have a client that creates replicas of their servers and names them <servername>_0, _1, _2. etc. I wrote a script to collect database sizes, table sizes, row counts and then each day run some reports on the changes. I only collect the data from the replicas and not the primary server but wanted to remove the replica names and only refer to the "cluster" name.

MySQL REPLACE() replaces all the occurrences of a substring within a string.
https://www.w3resource.com/mysql/string-functions/mysql-replace-function.php

Here is my example query that will give me the last 30 days of data:

SELECT
ss.server_name as server_name,
REPLACE(REPLACE(REPLACE(REPLACE(ss.server_name, '_2', ''),'_1',''),'_0',''),'_3','') as server_name,
ss.total_size_mb as total_size_mb,
ss.date_created,
ss.date as date
FROM growth_stats_lmp.schema_stats ss
WHERE ss.minutes_since_last_timestamp IS NOT NULL -- Removes the entries from a first run of growth_stats collection
AND ss.date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
GROUP BY server_name,schema_name,date;

The replace function will remove the _2, _1, _0, _3 out of the servername column (if it finds those values) and display only the remaining text that hasn't been "replaced out".

No comments:

Post a Comment