Skip to content

Instantly share code, notes, and snippets.

View benhamilton's full-sized avatar
👨‍💻
Getting it done

Ben Hamilton benhamilton

👨‍💻
Getting it done
9 followers · 9 following
View GitHub Profile
@benhamilton
benhamilton / replace-sub-string.sql
Created September 11, 2014 01:27
Replace a sub string within a string i.e. someone has peppered the contact descriptions with 'Brisvegas' instead of the correct city name of 'Brisbane'.
/*
source: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace
note: it's case sensitive
*/
UPDATE contacts
SET description = REPLACE(description, 'Brisvegas', 'Brisbane');
@benhamilton
benhamilton / mysql-show-full-name.sql
Created September 1, 2014 03:48
Select the 'salutation', 'first' and 'last' names, trim the whitespace out.
SELECT
LTRIM(
RTRIM(
CONCAT_WS(' ',
IFNULL(contacts.salutation, ''),
IFNULL(contacts.first_name, ''),
IFNULL(contacts.last_name, '')
)
)
) as 'Full Name'
@benhamilton
benhamilton / extvardef.php
Last active August 29, 2015 14:05
I found this snippet here: http://forums.sugarcrm.com/f6/mass-update-checkbox-field-43879/#post277132
<?php
$dictionary['Module_Name']['fields']['field_name']['massupdate'] = true;
?>
@benhamilton
benhamilton / get_next_task_number.sh
Created July 21, 2014 00:58
#!/bin/bash
source ~/last_task_number.txt
((TaskNumber++))
echo $TaskNumber
echo "TaskNumber=$TaskNumber" > ~/last_task_number.txt
@benhamilton
benhamilton / select-duplicate-contacts-sugarcrm.sql
Created July 8, 2014 05:58
/* MySQL to select and show duplicate CONTACTS in SugarCRM */
SELECT
concat_ws(' ',first_name,last_name),
phone_work,
phone_mobile,
deleted
FROM contacts
WHERE concat_ws(' ',first_name,last_name) IN (
SELECT concat_ws(' ',first_name,last_name)
FROM contacts
@benhamilton
benhamilton / count_records.sql
Last active August 29, 2015 14:01
Example how to count how many records contain specific value, then calculate what percentage that is of the total.
SELECT
SUM(IF(field_name = 'value_one',1,0)) AS 'Value One',
SUM(IF(field_name = 'value_two',1,0)) AS 'Value Two',
ROUND(SUM(IF(field_name = 'value_one',1,0)) / (SUM(IF(field_name = 'value_one',1,0)) + SUM(IF(field_name = 'value_two',1,0))) * 100) AS 'Percentage'
FROM table_name