CODEWITHSUNDEEP
sql-servert-sqlphone-number
Paul Prewett
Paul Prewett

Reputation: 2033

How to identify records that contain a phone number

I have the need to identify records in my database that contain a phone number so that I can send them on to a contact team.

Example: tblData

Given this table structure, the query might be:

SELECT * FROM tblData WHERE comment [HeresWhereINeedHelp]

The comment might (and likely will) contain all sorts of other data. An example comment:

Yea, I had a terrible experience. I'd like for someone to call me at 111.222.3333. Thank you.

The record containing this comment should be pulled in the query because it contains a phone number.

I tried an extended SPROC that enabled regex searching, but the performance was terrible. The system is SQL Server 2012.

Many thanks for any direction.

Upvotes: 1

Views: 2749

Answers (2)

paparazzo
paparazzo

Reputation: 45096

I would create a separate table for phone numbers stored as 10 digit integer.
Run Regex once to parse out the phone numbers.
Index that column and you will get index seek speed.

Upvotes: 0

mikurski
mikurski

Reputation: 1363

You should look at the LIKE operator. In your case, you're probably going to go for something along the lines of 

WHERE comment LIKE '%[0-9][0-9][0-9]_[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]%'

Since you're searching anywhere in the string, this will also take a long time to process over a large dataset - another option you might want to try is checking the comment field for a phone number when it's entered and then flagging that row using a column like ContainsPhoneNumber (bit). That way, you can index on that column and do faster lookups.

Upvotes: 4

Related Questions