SQL query to extract essential info from addressbook in iOS backup

ios-addressbook.sql

--
-- This is SQL query to extract AddressBook info from respective sqlite3 file from iOS backup.
-- It has been tested only on my own backup, so, if anything is wrong, fork, edit, and stuff.
--
-- # parse-manifest.py from http://stackoverflow.com/questions/3085153/how-to-parse-the-manifest-mbdb-file-in-an-ios-4-0-itunes-backup
--
-- $ python ../parse-manifest.py | grep AddressBook.sqlitedb
-- -rw-r--r-- 000001f5 000001f5 1654784 1312812502 1312812502 1287387943 (31bb7ba8914766d4ba40d6dfb6113c8b614be442)HomeDomain::Library/AddressBook/AddressBook.sqlitedb
--
-- $ sqlite3 31bb7ba8914766d4ba40d6dfb6113c8b614be442
--

select ABPerson.ROWID
     , ABPerson.first
     , ABPerson.last
     , ABPerson.Organization as organization
     , ABPerson.Department as department
     , ABPerson.Birthday as birthday
     , ABPerson.JobTitle as jobtitle

     , (select value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and label = (select ROWID from ABMultiValueLabel where value = '_$!<Work>!$_')) as phone_work
     , (select value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and label = (select ROWID from ABMultiValueLabel where value = '_$!<Mobile>!$_')) as phone_mobile
     , (select value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and label = (select ROWID from ABMultiValueLabel where value = '_$!<Home>!$_')) as phone_home

     , (select value from ABMultiValue where property = 4 and record_id = ABPerson.ROWID and label is null) as email
     
     , (select value from ABMultiValueEntry where parent_id in (select ROWID from ABMultiValue where record_id = ABPerson.ROWID) and key = (select ROWID from ABMultiValueEntryKey where lower(value) = 'street')) as address
     , (select value from ABMultiValueEntry where parent_id in (select ROWID from ABMultiValue where record_id = ABPerson.ROWID) and key = (select ROWID from ABMultiValueEntryKey where lower(value) = 'city')) as city
  from ABPerson
order by ABPerson.ROWID
;

Excellent !! Merci ...

Awesome! Thanks!

Thanks

Thank you very much - saved me a lot time figuring that out :-) Buy yourself a beer!

This was excellent.

I used it on an end-to-end post about recovering contacts from an iPhone backup file.
https://discussions.apple.com/message/31823429#message31823429

For some reason I had to get rid of the 'label is null' part for email, but otherwise it worked beautifully.

Thanks!

And the thanks go on and on - thank you

Thank you. It saved me quite a lot of time.

You're my hero!

Thank you very much!

The Note field possibly having 'CR' and 'LF' imbedded try using:
Replace (Replace(ABPerson.Note, x'0A', ' '),x'0D',' ')as Note