want to lookup a phone number in a db

A3

unread,

Oct 18, 2021, 9:37:24 AM10/18/21

to web2py-users

Want to lookup a phone number in a db table

the value to lookup is e.g. caller = "+31123456789"

Phone numbers in db field can contain + - . and also () and is not very consistent.

could look like: phonenumber = "+31-(0)123 456 789"

would like something:

db(db.table.phonenumber.like(caller)).select()

Any suggestions?

A3

unread,

Oct 19, 2021, 3:17:55 AM10/19/21

to web2py-users

finally I tried:

db((db.table.id >0)).select(db.table.phonenumber).find(lambda rw: rw.phonenumber.replace("-","").replace(" ","").replace("(0)","")==caller if rw is not None else None)
Initially this gave an error TypeError: 'NoneType' object has no attribute '__getitem__'

The problem was that the field phonenumber could also be None.

Solution: in the table definition :set default to "" and update all existing None values to ""

Any other suggestions?

Op maandag 18 oktober 2021 om 15:37:24 UTC+2 schreef A3:

Jim S

unread,

Oct 19, 2021, 10:50:13 AM10/19/21

to web2py-users

I don't think this is a good solution because it is retrieving all the rows from the database and then iterating over them to find a match.

Do you have the option of sanitizing a phone number before it is stored in the database? Or, could you create a new 'sanitized' phone number field that you could select over?

Either that, or take the value you're looking for an parse it in to all the possible ways it could be stored and then search for those options.

Ex

phone_numbers = ['+31123456789', '+31-(0)123 456 789', '+31-0 123 456 789', etc.]

db(db.table.phonenumber.belongs(phone_numbers)).select()

-Jim

A3

unread,

Oct 19, 2021, 12:23:41 PM10/19/21

to web2py-users

Thanks Jim,

Didn't realize that it will retrieve all. Actually the database is not so big at the moment so I don't notice any performance problems.