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.
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.