Android Contacts – [MUST HAVE ROOT]

If you have rooted your phone, there are various ways to run these queries but I choose two ways that work for me.

These code snippets will assist you in cleaning up your Android contacts.

DISCLAIMER: Backup your contacts before you run any of these queries as I take no responsibility for anything that happens to your contacts or phone.

If you do not already know how the Android contact database works it’d be a good idea to download SQLite Editor free or paid version and simply view the database before you run any of these queries as the tables are not as easy as doing “SELECT Last_Name, First_Name, Organization FROM contacts”

If you are in SQLite Editor select “Contacts Storage” from the Apps list and then choose “contacts2.db”, from there you’ll see a few tables.

Only Using Phone/Tablet

1. Download Google Keep and SQLite Database Editor

Using Phone/Tablet Along With A Computer

1. Download SQLite Database Editor, AndroPHP, and phpliteadmin

If you are in SQLite Database Editor, on most Android phones the contacts database is located at “/data/data/com.android.providers.contacts/databases/contacts.db” or around there.

SELECT data1
FROM data
INNER JOIN mimetypes ON mimetype_id = mimetypes._id
WHERE mimetype LIKE "%note%"
	AND length(data1) > 0;

SELECT data1
FROM data
INNER JOIN mimetypes ON mimetype_id = mimetypes._id
WHERE mimetype LIKE "%postal%"
	AND length(data1) > 0;

SELECT REPLACE(replace(REPLACE(data1, x '0D0A', x '0A'), x '0D', x '0A'), x '0A', ' ')
FROM data
INNER JOIN mimetypes ON mimetype_id = mimetypes._id
WHERE mimetype LIKE "%postal%"
	AND length(data1) > 0;

This query below will display all company/organization names in your contacts. Respectfully if you want only the distinct (no duplicates) you can remove the — in front of DISTINCT

SELECT
--DISTINCT
data1
FROM data
INNER JOIN mimetypes ON mimetype_id = mimetypes._id
WHERE mimetype LIKE "%organization%"
	AND length(data1) > 0;

SELECT data1 AS Company
	,(
		SELECT data1
		FROM data dt2
		INNER JOIN mimetypes ON mimetype_id = mimetypes._id
		WHERE mimetype LIKE "%name%"
			AND length(data1) > 0
			AND dt1.raw_contact_id = dt2.raw_contact_id
		) AS NAME
FROM data dt1
INNER JOIN mimetypes ON mimetype_id = mimetypes._id
WHERE mimetype LIKE "%organization%"
	AND length(data1) > 0
ORDER BY Company

SELECT data1 AS NAME
	,(
		SELECT data1
		FROM data dt2
		INNER JOIN mimetypes ON mimetype_id = mimetypes._id
		WHERE mimetype LIKE "%organization%"
			AND length(data1) > 0
			AND dt1.raw_contact_id = dt2.raw_contact_id
		) AS Company
FROM data dt1
INNER JOIN mimetypes ON mimetype_id = mimetypes._id
WHERE mimetype LIKE "%name%"
	AND length(data1) > 0
ORDER BY NAME

If you run into a situation like what I often do this query below will help as it looks at the name of the contact and the company name and lists where they appear to match

SELECT data1 AS NAME
	,data2 AS FirstName
	,data3 AS Lastname
	,(
		SELECT data1
		FROM data dt2
		INNER JOIN mimetypes ON mimetype_id = mimetypes._id
		WHERE mimetype LIKE "%organization%"
			AND length(data1) > 0
			AND dt1.raw_contact_id = dt2.raw_contact_id
		) AS Company
FROM data dt1
INNER JOIN mimetypes ON mimetype_id = mimetypes._id
WHERE mimetype LIKE "%name%"
	AND length(data1) > 0
	AND NAME LIKE Company
ORDER BY NAME

If you want to correct the problem in your contacts from the query above you can run this query

UPDATE data
SET data1 = NULL, data2 = NULL, data3 = NULL
WHERE mimetype_id IN (
		SELECT _id
		FROM mimetypes
		WHERE mimetype LIKE "%name%"
		)
AND raw_contact_id IN

(
SELECT
raw_contact_id

FROM data dt1
INNER JOIN mimetypes ON mimetype_id = mimetypes._id
WHERE mimetype LIKE "%name%"
    AND length(data1) > 0
    AND data1 LIKE (
        SELECT data1
        FROM data dt2
        INNER JOIN mimetypes ON mimetype_id = mimetypes._id
        WHERE mimetype LIKE "%organization%"
            AND length(data1) > 0
            AND dt1.raw_contact_id = dt2.raw_contact_id
        ));