#SELECT * FROM spcsta5_floorplans.`tblContacts` WHERE con_pobox is not null AND con_pobox != ''; #257
SELECT * FROM `tblContacts` WHERE con_pobox LIKE '%PO Box%';
#SELECT * FROM spcsta5_floorplans.`tblContacts` WHERE con_address LIKE 'PO Box%' AND con_pobox is null;#47
#UPDATE tblContacts SET con_address = "512 Meadow Street", con_pobox= "PO Box 226" WHERE `ID` = 570;
SELECT * FROM `tblContacts` WHERE con_address LIKE 'PO Box%';# AND con_pobox is null;
SELECT * FROM `tblContacts` WHERE con_pobox is not null AND con_pobox != '';
#SELECT * FROM `tblContacts` WHERE con_pobox is not null AND con_address is not null; #252
#SELECT * FROM `tblContacts` WHERE con_pobox is null AND con_address is not null; #525
#SELECT * FROM `tblContacts` WHERE con_pobox is not null AND con_pobox != '' AND con_address is null; #4

#SELECT * FROM `tblContacts` WHERE (con_pobox is null OR con_pobox = '')
AND (con_address is null OR con_address = '');#1424 2069-1424= 645

(2023, 2243, 2257, 2258, 2411, 2421, 2729, 2738, 4051)

UPDATE tblContacts SET con_address = "140 Pine Hill Road", con_pobox= "PO Box 190" WHERE `ID` = 2258;
UPDATE tblContacts SET con_address = "388 Somersworth Road", con_pobox= "PO Box 819" WHERE `ID` = 2411;
UPDATE tblContacts SET con_address = "320 Ocean House Road", con_pobox= "PO Box 6267" WHERE `ID` = 2421;
UPDATE tblContacts SET con_address = "6 Elm Street", con_pobox= "PO Box 566" WHERE `ID` = 2729;
UPDATE tblContacts SET con_address = "408 Main Street", con_pobox= "PO Box 279" WHERE `ID` = 2738;
UPDATE tblContacts SET con_address = "320 Ocean House Road", con_pobox= "PO Box 6260" WHERE `ID` = 4051;
UPDATE tblContacts SET con_address = "512 Meadow Street", con_pobox= "PO Box 226" WHERE `ID` = 570;

UPDATE tblContacts SET con_address = "408 Main Street", con_pobox= "PO Box 279" WHERE `ID` = 2738;
#UPDATE tblContacts SET con_pobox= NULL WHERE `ID` = 1455;
UPDATE tblContacts SET con_pobox= NULL WHERE `ID` = 3679;

UPDATE tblContacts SET zipcode = "07974" WHERE `ID` = 493;
UPDATE tblContacts SET con_address = "43 Merdolia Rd", con_pobox= "PO Box 97" WHERE `ID` = 70;



/*****UPDATE organization*****/
UPDATE organization SET address_2 = 'PO Box 729' WHERE id = 200;
UPDATE organization SET address_2 = 'PO Box 159' WHERE id = 234;
UPDATE organization SET address_2 = 'PO Box 896' WHERE id = 240;
UPDATE organization SET address_2 = 'PO Box 60' WHERE id = 15;
UPDATE organization SET address_2 = 'PO Box 240' WHERE id = 302;
UPDATE organization SET address_2 = 'PO Box 356' WHERE id = 277;
UPDATE organization SET address = NULL WHERE address = '';
UPDATE organization SET address_2 = NULL WHERE address_2 = '';


UPDATE organization SET address = 'PO Box 580' WHERE id = 98;
UPDATE organization SET address = 'PO Box 955' WHERE id = 146;
UPDATE organization SET address = 'PO Box 819' WHERE id = 88;
UPDATE organization SET address = 'PO Box 279' WHERE id = 81;
UPDATE organization SET address = 'PO Box 190' WHERE id = 56;
UPDATE organization SET address = 'PO Box 687' WHERE id = 55;
UPDATE organization SET address = 'PO Box 566' WHERE id = 39;
UPDATE organization SET address = 'PO Box 6260' WHERE id = 23;
UPDATE organization SET address = 'PO Box 6267' WHERE id = 22;
UPDATE organization SET address = 'PO Box 375' WHERE id = 1783;
UPDATE organization SET address = 'PO Box 2322' WHERE id = 1786;
UPDATE organization SET address = 'PO Box 263' WHERE id = 1799;
UPDATE organization SET address = 'PO Box 119' WHERE id = 1809;
UPDATE organization SET address = 'PO Box 1638' WHERE id = 1811;

UPDATE organization SET address = '431 US Rte 1', address_2 = 'PO Box 9'  WHERE id = 1716;
UPDATE organization SET address = '357 Tuttle Rd', address_2 = 'PO Box 6A'  WHERE id = 1726;
UPDATE organization SET address = '79 Blake St Ste 1', address_2 = 'PO Box 1118'  WHERE id = 1739;


SELECT id owner_id, 'B' address_type, 'O' owner_type, address, address2, city, state, zip_code, county,
NOW() created_date, 1271, NOW() modified_date, 1271
FROM organization_12_31_19
WHERE address2 NOT LIKE('PO Box%');

UPDATE organization_12_31_19 SET address = 'PO Box 229', address_2 = '38 Jacksonville Road' WHERE id IN(29)
UPDATE organization_12_31_19 SET address = 'PO Box 60', address_2 = '1081 Eagle Lake Road' WHERE id IN(15)
UPDATE organization_12_31_19 SET address = 'PO Box 510', address_2 = '19 Haverhill Rd' WHERE id IN(1601)
UPDATE organization_12_31_19 SET address = 'PO Box 1588', address_2 = '4 Lund Ln' WHERE id IN(1610)

UPDATE organization_12_31_19 SET address = 'PO Box 600', address_2 = 'East St' WHERE id IN(1622)
UPDATE organization_12_31_19 SET address = 'PO Box 789', address_2 = '547 US Route 4' WHERE id IN(1631)
UPDATE organization_12_31_19 SET address = 'PO Box 287', address_2 = '300 Route 10 South' WHERE id IN(1632)
UPDATE organization_12_31_19 SET address = 'PO Box 9', address_2 = '431 US Rte 1' WHERE id IN(1716)

UPDATE organization_12_31_19 SET address = 'PO Box 1118', address_2 = '79 Blake St Ste 1' WHERE id IN(1739)
UPDATE organization_12_31_19 SET address = 'PO Box 6A', address_2 = '357 Tuttle Rd' WHERE id IN(1726)

UPDATE organization_12_31_19 SET address = 'PO Box 58', address_2 = '12 Municipal Way' WHERE id IN(1776)

UPDATE organization_12_31_19 SET address = 'PO Box 325', address_2 = '6A Lion's Way' WHERE id IN(1839)
UPDATE organization_12_31_19 SET address = 'PO Box 130', address_2 = '80 Main Street' WHERE id IN(1872)







organization - phone and fax numbers updated
#UPDATE organization SET FaxNumber = NULL WHERE FaxNumber = 'N/A';


2/5/2022 - Copied organization to  new staging table, organization_cleaned_2_5_22, then performed these updates to the new table 

SELECT id, PhoneNumber, REPLACE(REPLACE(PhoneNumber, '(', ''), ')', '') fixedNum,
FaxNumber FROM organization WHERE PhoneNumber LIKE '%(%';
UPDATE organization SET PhoneNumber = REPLACE(REPLACE(PhoneNumber, '(', ''), ')', '') WHERE PhoneNumber LIKE '%(%';

SELECT id, PhoneNumber, REPLACE(REPLACE(FaxNumber, '(', ''), ')', '') fixedNum,
FaxNumber FROM organization WHERE FaxNumber LIKE '%(%';
UPDATE organization SET FaxNumber = REPLACE(REPLACE(FaxNumber, '(', ''), ')', '') WHERE FaxNumber LIKE '%(%';

SELECT id, PhoneNumber, REPLACE(REPLACE(PhoneNumber, '-', ''), '-', '') fixedNum,
FaxNumber FROM organization WHERE PhoneNumber LIKE '%-%';
#UPDATE organization SET PhoneNumber = REPLACE(REPLACE(PhoneNumber, '-', ''), '-', '') WHERE PhoneNumber LIKE '%-%';

SELECT id, PhoneNumber, REPLACE(REPLACE(FaxNumber, '-', ''), '-', '') fixedNum,
FaxNumber FROM organization WHERE FaxNumber LIKE '%-%';
#UPDATE organization SET FaxNumber = REPLACE(REPLACE(FaxNumber, '-', ''), '-', '') WHERE FaxNumber LIKE '%-%';

SELECT id, PhoneNumber, REPLACE(REPLACE(PhoneNumber, ' ', ''), ' ', '') fixedNum,
FaxNumber FROM organization WHERE PhoneNumber LIKE '% %';

#UPDATE organization SET PhoneNumber = REPLACE(REPLACE(PhoneNumber, ' ', ''), ' ', '') WHERE PhoneNumber LIKE '% %';

SELECT id, PhoneNumber, REPLACE(REPLACE(FaxNumber, ' ', ''), ' ', '') fixedNum,
FaxNumber FROM organization WHERE FaxNumber LIKE '% %';
#UPDATE organization SET FaxNumber = REPLACE(REPLACE(FaxNumber, ' ', ''), ' ', '') WHERE FaxNumber LIKE '% %';



phones -
Updated organization
SELECT DISTINCT LENGTH(PhoneNumber) FROM organization_12_31_19;
SELECT DISTINCT LENGTH(FaxNumber) FROM organization_12_31_19;
SELECT * FROM organization_12_31_19 WHERE LENGTH(PhoneNumber) = 15;
SELECT * FROM organization_12_31_19 WHERE id = 1826;


#UPDATE organization_12_31_19 SET PhoneNumber = NULL WHERE PhoneNumber = '';
#UPDATE organization_12_31_19 SET FaxNumber = NULL WHERE FaxNumber = '';
#UPDATE organization_12_31_19 SET FaxNumber = NULL WHERE FaxNumber = 'N/A';

#UPDATE organization SET county = NULL WHERE county = '';
#SELECT * FROM organization WHERE county = '';


#UPDATE tblContacts SET `Business Phone` = RIGHT(`Business Phone`, 10) WHERE `ID` IN(593, 880, 1009, 1187, 1731, 2054); #18024335818
Updated tblContacts
SELECT DISTINCT LENGTH(`Business Phone`) FROM tblContacts;

SELECT `ID`, `Business Phone`, fax, `Mobile Phone` FROM tblContacts
WHERE LENGTH(`Business Phone`) = 0;
#UPDATE tblContacts SET `Business Phone` = NULL WHERE LENGTH(`Business Phone`) = 0;


SELECT `ID`, `Business Phone`, REPLACE(REPLACE(`Business Phone`, '(', ''), ')', '')fixedNum,
fax FROM tblContacts WHERE `Business Phone` LIKE '%(%';
#UPDATE tblContacts SET `Business Phone` = REPLACE(REPLACE(`Business Phone`, '(', ''), ')', '') WHERE `Business Phone` LIKE '%(%';


SELECT `ID`, `Mobile Phone`, REPLACE(REPLACE(`Mobile Phone`, '(', ''), ')', '')fixedNum,
fax FROM tblContacts WHERE `Mobile Phone` LIKE '%(%';
UPDATE tblContacts SET `Mobile Phone` = REPLACE(REPLACE(`Mobile Phone`, '(', ''), ')', '') WHERE `Mobile Phone` LIKE '%(%'; #99

SELECT `ID`, `Business Phone`, REPLACE(REPLACE(fax, '(', ''), ')', '')fixedNum,
fax FROM tblContacts WHERE fax LIKE '%(%';
UPDATE tblContacts SET fax = REPLACE(REPLACE(fax, '(', ''), ')', '') WHERE fax LIKE '%(%'; #403#UPDATE tblContacts SET `Business Phone` = '2074887700' WHERE `ID` = 115; #this was originally 1-207-488-7700
#UPDATE tblContacts SET `Business Phone` = NULL WHERE `ID` = 1578; #orig val = 207

SELECT id, bldg_phone, REPLACE(REPLACE(bldg_phone, '(', ''), ')', '')fixedNum, bldg_fax
FROM `buildings` WHERE bldg_phone LIKE '%(%';

#UPDATE `buildings` SET bldg_phone = REPLACE(REPLACE(bldg_phone, '(', ''), ')', '') WHERE bldg_phone LIKE '%(%'; #344

SELECT id, bldg_phone, REPLACE(REPLACE(bldg_phone, '-', ''), '-', '') fixedNum
FROM `buildings` WHERE bldg_phone LIKE '%-%';
#UPDATE `buildings` SET bldg_phone = REPLACE(REPLACE(bldg_phone, '-', ''), '-', '') WHERE bldg_phone LIKE '%-%'; #436

SELECT id, bldg_phone, REPLACE(bldg_phone, ' ', '') fixedNum
FROM `buildings` WHERE bldg_phone LIKE '% %';
#UPDATE `buildings` SET bldg_phone = REPLACE(bldg_phone, ' ', '') WHERE bldg_phone LIKE '% %'; #345

SELECT DISTINCT LENGTH(bldg_phone) FROM `buildings`;

SELECT * FROM `buildings` WHERE LENGTH(bldg_phone) = 10;
#UPDATE `buildings` SET bldg_phone = NULL WHERE bldg_phone = ''; #5
#UPDATE `buildings` SET bldg_phone = NULL WHERE bldg_phone = 'w'; #only 1 for id 1435
#UPDATE `buildings` SET bldg_phone = NULL WHERE bldg_phone = 'NoLongerinUse'; #only 1 for id 1139
#UPDATE `buildings` SET bldg_phone = '2072824131' WHERE id = 83; #only 1 had a 4 in front of the 207 (42072824131)




UPDATE tblContacts SET Extension = `Business Phone` WHERE `ID` IN(967, 2047); #orig vals 1213, 1294 for Business Phone moved to Extension
UPDATE tblContacts SET `Business Phone` = NULL WHERE `ID` IN(967, 2047);

//Moved extensions to the Extension field
UPDATE tblContacts SET `Business Phone` = '6038837765', Extension = '1' WHERE `Id` = 199;
UPDATE tblContacts SET `Business Phone` = '6039343108', Extension = '441' WHERE `Id` = 202;
UPDATE tblContacts SET `Business Phone` = '6039262000', Extension = '130' WHERE `Id` = 422;
UPDATE tblContacts SET `Business Phone` = '6038633540', Extension = '105' WHERE `Id` = 472;
UPDATE tblContacts SET `Business Phone` = '2078272252', Extension = '112' WHERE `Id` = 1139;
UPDATE tblContacts SET `Business Phone` = '6038633540', Extension = '110' WHERE `Id` = 1167;
UPDATE tblContacts SET `Business Phone` = '2075822590', Extension = '108' WHERE `Id` = 1581;
UPDATE tblContacts SET `Business Phone` = '6039264560', Extension = '5' WHERE `Id` = 421;
UPDATE tblContacts SET `Business Phone` = '8005154242' WHERE `Id` = 701;
UPDATE tblContacts SET `Mobile Phone` = '7232532' WHERE `Id` = 869; #orig val CELL 723-2532

SELECT `ID`, `Business Phone`, fax, `Extension`, REPLACE(REPLACE(`Business Phone`, ' ', ''), ' ', '') fixedNum
 FROM tblContacts WHERE `Business Phone` LIKE '% %';

#UPDATE tblContacts SET `Business Phone` = REPLACE(REPLACE(`Business Phone`, ' ', ''), ' ', '') WHERE `Business Phone` LIKE '% %';


SELECT `ID`, `Mobile Phone`, fax, `Extension`, REPLACE(REPLACE(`Mobile Phone`, ' ', ''), ' ', '') fixedNum
 FROM tblContacts WHERE `Mobile Phone` LIKE '% %';

UPDATE tblContacts SET `Mobile Phone` = REPLACE(REPLACE(`Mobile Phone`, ' ', ''), ' ', '') WHERE `Mobile Phone` LIKE '% %'; #99

SELECT `ID`, `Business Phone`, fax, `Extension`, REPLACE(REPLACE(fax, ' ', ''), ' ', '') fixedNum
 FROM tblContacts WHERE fax LIKE '% %';

UPDATE tblContacts SET fax = REPLACE(REPLACE(fax, ' ', ''), ' ', '') WHERE fax LIKE '% %'; #403

SELECT id, `Business Phone`, REPLACE(REPLACE(`Business Phone`, '-', ''), '-', '') fixedNum,
fax FROM tblContacts WHERE `Business Phone` LIKE '%-%';
#UPDATE tblContacts SET `Business Phone` = REPLACE(REPLACE(`Business Phone`, '-', ''), '-', '') WHERE `Business Phone` LIKE '%-%';#1587

SELECT id, `Business Phone`, REPLACE(REPLACE(fax, '-', ''), '-', '') fixedNum,
fax FROM tblContacts WHERE fax LIKE '%-%';
UPDATE tblContacts SET fax = REPLACE(REPLACE(fax, '-', ''), '-', '') WHERE fax LIKE '%-%';#416


SELECT id, `Mobile Phone`, REPLACE(REPLACE(`Mobile Phone`, '-', ''), '-', '') fixedNum,
fax FROM tblContacts WHERE `Mobile Phone` LIKE '%-%';
UPDATE tblContacts SET `Mobile Phone` = REPLACE(REPLACE(`Mobile Phone`, '-', ''), '-', '') WHERE `Mobile Phone` LIKE '%-%';#110

/*users table phone format data*/
SELECT user_id, phone, REPLACE(REPLACE(phone, '-', ''), '-', '') fixedNum
 FROM users WHERE phone LIKE '%-%';
#UPDATE users SET phone = REPLACE(REPLACE(phone, '-', ''), '-', '') WHERE phone LIKE '%-%';


#UPDATE tblContacts SET fax = NULL WHERE fax = ''; #167
#UPDATE tblContacts SET `Mobile Phone` = NULL WHERE `Mobile Phone` = ''; #167

#UPDATE spcsta5_floorplans.current_devices SET `Condition` = NULL WHERE `Condition` = ''; #9

#UPDATE buildings SET address = REPLACE(address, 'P.O.', 'PO') WHERE address LIKE '%P.O. Box%';