mysql: extract domain from email
SUBSTR(email_addresses.email_address, INSTR(email_addresses.email_address, '@') + 1)
Category: MySQL
mysql: capitalize first word of all words
credits to: http://joezack.com/2008/10/20/mysql-capitalize-function/ create a function: CREATE FUNCTION CAP_FIRST (input VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC BEGIN DECLARE len INT; DECLARE i INT; SET len = CHAR_LENGTH(input); SET input = LOWER(input); SET i = 0; WHILE (i < len) DO […]
mysql: min value per group
surprisingly, to find the min date per company, which is a super basic query, on mysql you need to do a complicated query. SELECT a.id, a.company, a.email, a.custom_last_email FROM leads a INNER JOIN ( SELECT company, MIN(custom_last_email) mindate FROM […]
mysql: remove only_full_group_by and make it persistent
update it from the console (will revert upon reboot) from the command line go to the mysql console mysql then edit the sql_mode entry SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); SELECT @@sql_mode; SELECT @@GLOBAL.sql_mode; for a persistent change, in mysql […]
phpmyadmin: deprecation notice
sudo apt-get remove --purge phpmyadmin php-gettext php-mbstring -y sudo apt-get autoremove -y sudo apt-get update sudo apt-get install phpmyadmin php-gettext php-mbstring -y
phpmyadmin: securing web console
I installed PHPMyadmin to check my help manage my database, eventhough I also use the desktop program MySQL Workbench, I fell sometimes its important to use the webconsole. Recently, upon checking my MySQL Logs (/var/logs/mysql/error.log) I encountered that everyday I […]
mysql: enable logging
mysql> show variables like '%log%'; Recreate the log files I deleted by mistake. It appears the MySQL does not recreate them automatically. touch /var/log/mysql.log chown mysql:adm /var/log/mysql.log touch /var/log/mysql/error.log chown mysql:adm /var/log/mysql/error.log touch /var/log/mysql/mysql-slow.log chown mysql:adm /var/log/mysql/mysql-slow.log touch /var/log/mysql/mysql.log chown […]
csv import manual
Remove Commas from strings Truncate String Length using LEFT function in Excel Use CLEAN Function to remove unwanted breaks and non displaying characters
sql query: remove leading and trailing double quotes
UPDATE m_product SET name = TRIM(BOTH '"' FROM name) WHERE m_product_id = '1128493' UPDATE m_product set name = replace(name, '""', '"') WHERE m_product_id = '1128493'
mysql: assign opportunities to user based on parent account
Query to Select List: SELECT * FROM opportunities LEFT JOIN accounts_opportunities ON accounts_opportunities.opportunity_id = opportunities.id LEFT JOIN accounts ON accounts_opportunities.account_id = accounts.id WHERE accounts.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86'; UPDATE Query: UPDATE opportunities x LEFT JOIN accounts_opportunities y ON y.opportunity_id […]