mysql: extract domain from email
SUBSTR(email_addresses.email_address, INSTR(email_addresses.email_address, '@') + 1)
Category: Databases
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: backup databases
Backups all databases mysqldump -u [user] -p[password] --all-databases > [filename].sql specific databases mysqldump -u [user] -p[password] [database_name] > [filename].sql specific database but omitting specific tables mysqldump -u [user] -p[password] [database_name] --ignore-table=db1.tbl1 --ignore-table=db2.tbl1 > [filename].sql restore mysql -u […]
pgsql: pgdump authentication
create .pg_pass file on the root folder of the user that will run the pgsql command for example. /root or /user touch /root/.pgpass chmod 600 .pgpass add the credentialas user and password that will be user […]
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 […]
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 […]
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
pgsql: create table inside database
Example CREATE TABLE c_ordernotes ( c_ordernotes_id numeric(10,0) CONSTRAINT firstkey PRIMARY KEY, ad_client_id numeric(10,0) NOT NULL, ad_org_id numeric(10,0) NOT NULL, isactive character(1), created timestamp DEFAULT current_timestamp, createdby numeric(10,0), updated timestamp DEFAULT current_timestamp, updatedby numeric(10,0), c_order_id numeric(10,0), line numeric(10,0), notes_name varchar(255), […]
delete duplicate rows on pgsql and mysql
This code shows how to delete duplicate rows on pgsql DELETE FROM m_product WHERE sku in( SELECT sku FROM ( SELECT sku, count(*) FROM m_product GROUP BY sku HAVING count(*) > 1 ) a ) AND em_mg_manufacturer = 1000012 […]
pgsql: replace add break
UPDATE m_product SET em_mg_longdescription = regexp_replace(em_mg_longdescription,'------------',chr(13)) WHERE em_mg_manufacturer = 1000637 AND value = 'IMI-YW356A01' Some additional replace scripts UPDATE m_product SET em_mg_longdescription = regexp_replace(em_mg_longdescription,'</li>',chr(10)) WHERE em_mg_manufacturer = 1000637 UPDATE m_product SET em_mg_longdescription = replace(em_mg_longdescription,'<li>','') WHERE em_mg_manufacturer […]