3

In SQL Server 2008 R2, I'm doing a full-text search for the term 'C#' like this:

SELECT *
FROM  Book b  
WHERE CONTAINS (b.title , 'c#')

However, the full text search looks like it strips out the pound sign and then performs the search.

If I do a wildcard "like" search, I get correct results:

SELECT *
FROM  Book b  
where title like '%c#%'

Is there a way to do a full-text CONTAINS search for a term that contains a hash/pound sign?

Trevor
  • 4,620
  • 2
  • 28
  • 37

2 Answers2

1

It sounds like the full text search is doing exactly what it's supposed to. To get an exact match change your where clause by putting the term C# in double quotes. Since an exact match is case sensitive by definition, you can use an or to search for both a lower case and upper case version.

WHERE CONTAINS(Name, '"C#" or "c#"');
Daniel Gimenez
  • 18,530
  • 3
  • 50
  • 70
  • That fixes it. I'd tried this before, but with a lowercase "c#" which didn't match anything. I guess CONTAINS is case-sensitive, but LIKE is not. – Trevor Sep 23 '14 at 21:48
  • I'll be honest I'm a bit confused by the documentation when I look at it. I'm glad this worked, but I'm curious if just upper casing 'C#' in your original answer would've worked? Anyways I changed my answer to support case sensitivity. – Daniel Gimenez Sep 24 '14 at 14:50
  • 2
    I can't seem to get it to work. My case is a little different. I'm searching for a word preceded by the `#` symbol, say `#mytag`. The results returned are only where `mytag` is a word on its own. i.e. with no hash symbol. Any idea what this could be? I read the hash symbol could possibly be used as a wildcard for numbers when used as prefix to a word... – Lukas Aug 06 '15 at 08:20
0

I don't have a sandbox to test this in but did you try CHAR(35) which is the pound sign? You could set a variable and concatenate it with CHAR(35), that may work.

Robert
  • 1,696
  • 3
  • 36
  • 70