Jan Gressmann Jan Gressmann - 1 year ago 134
SQL Question

SQL - Query Phonenumber that are stored inconsistently

we a phonenumber field in our database and I would like to do a simple lookup query like:

SELECT * FROM TABLE WHERE Phonenumber = '555123456'

But since the phonenumbers are entered by users and are not normalized, we don't really know what they look like.

Could be:

  • +555-123456


  • (555) 123 456


  • 555-12-34-56

or something complety different.

The only thing certain is that the all the given numbers should be there in the correct order. Is it possible to construct a query around that?

Answer Source

Since I don't know what RDBMS you're looking for, I'll give the most generic way:

phonenumber like '%5%5%5%1%2%3%4%5%6%'

This assumes that all phone numbers are at least equal length (in digits).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download