| There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator. |
About
This SQL identifies malformed phone numbers of Active Contacts. Malformed phone numbers are those that are not exactly 10 numeric digits.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- This SQL only searches active contacts.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select a.* , contact_role, denorm_no as contact_number, patno, fname as patient_first, lname as patient_last , status_pat from (
select id, last_name as contact_last, first_name as contact_first,
home_phone as phone, 'contact_homephone' as phonetype
from contact where
(home_phone not similar to '[0-9]{3}[-]{0,1}[0-9]{3}[-]{0,1}[0-9]{4}[ ]{0,}' and home_phone != '' and home_phone is not null and trim(home_phone) != '- -')
union
select id, last_name as contact_last, first_name as patient_first,
cell_phone as patient_homephone, 'contact_cellphone' as phonetype
from contact where
(cell_phone not similar to '[0-9]{3}[-]{0,1}[0-9]{3}[-]{0,1}[0-9]{4}[ ]{0,}' and cell_phone != '' and cell_phone is not null and trim(cell_phone) != '- -')
union
select id, last_name as contact_last, first_name as patient_first,
work_phone as patient_homephone, 'contact_workphone' as phonetype
from contact where
(work_phone not similar to '[0-9]{3}[-]{0,1}[0-9]{3}[-]{0,1}[0-9]{4}[ ]{0,}' and work_phone != '' and work_phone is not null and trim(work_phone) != '- -')
)a inner join register_contact rc on rc.contact_id = a.id
inner join register r on r.patno = rc.patno
where status_pat = 'ACTIVE' and contact_last != 'phone change' SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select a.* , contact_role, denorm_no as contact_number, patno, fname as patient_first, lname as patient_last , status_pat from (
select id, last_name as contact_last, first_name as contact_first,
home_phone as phone, 'contact_homephone' as phonetype
from contact where
(home_phone not similar to '[0-9]{3}[-]{0,1}[0-9]{3}[-]{0,1}[0-9]{4}[ ]{0,}' and home_phone != '' and home_phone is not null and trim(home_phone) != '- -')
union
select id, last_name as contact_last, first_name as patient_first,
cell_phone as patient_homephone, 'contact_cellphone' as phonetype
from contact where
(cell_phone not similar to '[0-9]{3}[-]{0,1}[0-9]{3}[-]{0,1}[0-9]{4}[ ]{0,}' and cell_phone != '' and cell_phone is not null and trim(cell_phone) != '- -')
union
select id, last_name as contact_last, first_name as patient_first,
work_phone as patient_homephone, 'contact_workphone' as phonetype
from contact where
(work_phone not similar to '[0-9]{3}[-]{0,1}[0-9]{3}[-]{0,1}[0-9]{4}[ ]{0,}' and work_phone != '' and work_phone is not null and trim(work_phone) != '- -')
)a inner join register_contact rc on rc.contact_id = a.id
inner join register r on r.patno = rc.patno
where status_pat = 'ACTIVE' and contact_last != 'phone change' 
