Ron Kidd
asked on
Set Full Text Search to treat Numbers at Text Sql Server 2008 R2
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
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
ASKER
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 "."
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 "."
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:.
If you wish to do the exact search use LIKE:
Open in new window