Sql Server 2008 - Fulltext Rounding Money Values?

Lets assume we have a full text indexed table with those records: blabla bla bla 101010,65 blabla bla bla blabla bla bla 1012344,34 blabla bla bla (The decimal separator in Port

Solution 1:

EDIT:

< Ignore > The reason you are seeing this behaviour is that, the default wordbreakers for SQL fulltext search are defined by the English language (locale 1033). In English, a comma is a valid word-breaker, thereby breaking your number into two different numbers. However, if you use the Portuguese word-breaker, FTS quite cleverly retains the numbers together. Try running the following query on your SQL Server to see how the fulltext engine parses the same input differently depending on the locale specified:

--use locale Englishselect*from sys.dm_fts_parser('"12345,10"',1033,NULL,0)
--use locale Portugueseselect*from sys.dm_fts_parser('"12345,10"',2070,NULL,0)

< /Ignore >

UPDATE: Alright, I have managed to replicate your scenario and yes it does seem to be default behaviour with SQL Server FTS. However, it only seems to round up to nearest 1/10th of the number (the nearest 10 centavos in your case), and NOT to the nearest whole number.

So for example; 12345,88 would be returned in searches for both 12345,88 as well as 12345,9, while 56789,98 would appear in searches for 56789,98 as well as 56790. However, a number such as 45678,60 will remain intact with no rounding up or down, so it's not as bad as you think.

Not sure if there is anything you can do to change this behaviour though. A quick search on Google returned nothing.

Solution 2:

My suggestion would be to not use the Money data type in the first place. All it buys you is a little formatting ease (which you should be doing at the presentation layer anyway), but brings about other complications and inflexibility. I'm not sure DECIMAL/NUMERIC would solve this particular issue, as I'm not a full-text guy, but I try to steer people away from problematic data types like MONEY whenever I can. See this previous question for lots of discussion about this. Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?