FullText index -> Getting records containing a phone number

Question

FullText index -> Getting records containing a phone number

patrick.fiche

Old Hand

Points: 363
More actions
September 17, 2008 at 9:32 am

#382683

Hi,
I'm storing some data (like SMS) in SQL Server 2005 table with a Full-text index defined on this column.
Is there any way to get all the records that contain numeric items (like phone numbers / account numbers)...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 1

You'd probably need to implement some regular expression search to do this. Plenty of CLR procs on the Internet that can help here.

Otherwise, not sure how you'd wildcard this.

patrick.fiche Old Hand Points: 363 More actions · Answer 2

Thanks for your answer.

But I'm afraid that in this case the defined Full-text index won't help me a lot...

And a standard index will be very inefficient and probably not used...

It would mean that a Seq Scan will be performed on my data.

Mike C SSC-Insane Points: 23224 More actions · Answer 3

On 2005, the full-text index is not going to help greatly. The problem is that you can't define patterns in full-text index search. You can search for numeric values using FTS, but you'd have to search for every combination from "200" to "999" and then "2000" to "9999" (leaving off leading zeroes and leading 1's, and assuming U.S. phone numbers). It also won't tell you where these occur in the document. What you probably need to do with this (awful as it is) is perform a LIKE using '%[2-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%' pattern match to find documents with a phone-number formatted string.

If this is a one-off type of deal this might be sufficient. If this is something you need to do a lot you might want to extract the phone numbers into another table with the PK of the related documents. If you have SQL 2008 there's a couple of new DMFs that allow you to see the full-text index content. This allows you to do some interesting things, like just grab all indexed tokens that are numeric. You can then join back to the source table to get the correct documents. I don't believe, however, that it will store the complete phone numbers as one large token.

patrick.fiche Old Hand Points: 363 More actions · Answer 4

OK, thanks for your answer.

I will investigate the new SQL Server 2008 possibilities just in case it becomes a priority for me to extract these phone numbers.