| 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 shows any malformed phone number associated with an active patient in the Basic Information window.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
Only malformed numbers (i.e. not 10 numeric digits) attached in a patient's chart are shown.
Only active patients are searched. To search all patient records for malformed numbers, see SQL: Invalid Phone Fields: Basic Information.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select a.* from (
select patno, lname as patient_last, fname as patient_first,birthdat, status_pat,
hphone as patient_homephone, 'patient_homephone' as phonetype
from register where
(hphone not similar to '[0-9]{3}[-]{0,1}[0-9]{3}[-]{0,1}[0-9]{4}[ ]{0,}' and hphone != '' and hphone is not null and trim(hphone) != '- -')
union
select patno, lname as patient_last, fname as patient_first,birthdat, status_pat,
bphone as patient_workphone, 'patient_workphone' as phonetype
from register where
(bphone not similar to '[0-9]{3}[-]{0,1}[0-9]{3}[-]{0,1}[0-9]{4}[ ]{0,}' and bphone != '' and bphone is not null and trim(bphone) != '- -')
union
select patno, lname as patient_last, fname as patient_first,birthdat, status_pat,
cell_phone as patient_cellphone, 'patient_cellphone' as phonetype
from register 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) != '- -')
)a where status_pat = 'ACTIVE'SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select a.* from (
select patno, lname as patient_last, fname as patient_first,birthdat, status_pat,
hphone as patient_homephone, 'patient_homephone' as phonetype
from register where
(hphone not similar to '[0-9]{3}[-]{0,1}[0-9]{3}[-]{0,1}[0-9]{4}[ ]{0,}' and hphone != '' and hphone is not null and trim(hphone) != '- -')
union
select patno, lname as patient_last, fname as patient_first,birthdat, status_pat,
bphone as patient_workphone, 'patient_workphone' as phonetype
from register where
(bphone not similar to '[0-9]{3}[-]{0,1}[0-9]{3}[-]{0,1}[0-9]{4}[ ]{0,}' and bphone != '' and bphone is not null and trim(bphone) != '- -')
union
select patno, lname as patient_last, fname as patient_first,birthdat, status_pat,
cell_phone as patient_cellphone, 'patient_cellphone' as phonetype
from register 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) != '- -')
) a where status_pat = 'ACTIVE'
