Solved

Set Full Text Search to treat Numbers at Text Sql Server 2008 R2

Posted on 2014-09-02
5
1,073 Views
Last Modified: 2014-09-07
Hello

I am testing setting up a Full Text Search and I am having issues when trying to search for a Numbers.

If I search for contains(ColumnA,'"5"')
It will only return rows that have "5"
It won't return rows that contain "5mm" or "5Ltr"

Can I make it treat all Numbers as Text?


Also if I search for contains(ColumnA,'"2.5m"')
It will return results that contain "2.5m" AND results that contain "25m" (It searches for the decimal point then drops it and joins the "2" and the "5m" together)

Thanks
0
Comment
Question by:p-plater
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40300235
To make the CONTAINS to search from the beginning of the string you need to use *, like this:
 contains(ColumnA,'"5*"')

Open in new window

Please note that punctuation is ignored in full text search (when you search for 2.5m the decimal point is a full stop punctuation as far as SQL Server is concerned ). here is the quote from MSDN:
Punctuation is ignored. Therefore, CONTAINS(testing, "computer failure") matches a row with the value, "Where is my computer? Failure to find it would be expensive." For more information on word-breaker behavior, see Configure and Manage Word Breakers and Stemmers for Search.
.
If you wish to do the exact search use LIKE:
ColumnA LIKE '%2.5m%'

Open in new window

0
 

Author Comment

by:p-plater
ID: 40300355
Hello Chaau

I Have been using '"5*'" and '"2.5m*"' - Just missed out putting it on the ticket!! Sorry.

I'm trying to use Full text Search as it is about 4 Times faster than Like on this project

There must be some way to make it search the "."
0
 
LVL 24

Expert Comment

by:chaau
ID: 40300723
All numbers are in the stop list. You can remove the numbers from there. However, it is very difficult to get full text to search "like Google". The punctuation is the trickiest part. I guess you need to either use LIKE or store the key words in an index table
0
 

Author Comment

by:p-plater
ID: 40302288
I have removed the Stop Words list from the Index.

My other Option is to Replace all full stops with something else Like |,^,~,<,>,{,},[,]

Do you know if any of these characters get dropped like the full stop?
0
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40302394
Fulltext indexes words. The "word" in its view is a continuous string of characters without spaces or punctuation. The occurrence of a non-alphanumeric character breaks a word. Even if you specify SET STOPWORD=OFF during index creation the punctuation will break "words". You can try replace the full stops but you need to replace them to alpha sequence, i.e. "qwerty" Then your word will become:
2.5 => 2qwerty5
I think this is the only way
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql help 8 55
SQL Server 2012 r2 Make faster Temp Table 17 103
SQL Insert parts by customer 12 31
why sql server only update some statistics in the database ? 3 19
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question