Geek Answers Handbook

SQL Server ampersand full text search (&)

have a problem with ampersand (&)

How to search for words (or sentences) containing ampersand (&).

For example, in a database:

1: "Johnson & Johnson"
2: "AT&T"
3: "Sample & Sample"

How to write a full text search to search for individual records?

SELECT * from Companies c WHERE CONTAINS(c.CompanyName, '"AT&T"')

I know that the character (&) is responsible for the logical operation I. But I do not know how to encode it for searching in the text using full-text search.

Any idea?

+5
source share
2 answers

Short version: You cannot (or at least you can, but you can get more results than expected)

: '&' " ", .. SQL Server '&', "" (.. ). SQL Server "AT&T" - , "AT" "T".

, sys.dm_fts_parser:

SELECT * FROM sys.dm_fts_parser('AT&T', 1033, 0, 0)

keyword     group_id    phrase_id   occurrence  special_term  display_term  expansion_type source_term
----------- ----------- ----------- ----------- ------------- ------------- -------------- -----------
0x00610074  1           0           1           Noise Word    at            0              AT
0x0074      2           0           1           Noise Word    t             0              T

, "AT&T" "AT T".

, ,

+5

. "". (&) , , , . "AT&T", :

DECLARE @Term NVARCHAR(100);
SET @Term = N'"AT&T"';

SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 1);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 1);
GO

:

keyword             group  phrase  occurrence  special      display  expansion  source
                    id     id                  term         term     type       term
0x0061007400260074  1      0       1           Exact Match  at&t     0          AT&T

, , ("), , woo hoo!

, "Johnson & Johnson":

DECLARE @Term NVARCHAR(100);
SET @Term = N'"Johnson & Johnson"';

SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 1);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 1);
GO

:

keyword                         group  phrase  occurrence  special      display  expansion  source
                                id     id                  term         term     type       term
0x006A006F0068006E0073006F006E  1      0       1           Exact Match  johnson  0          Johnson & Johnson
0x006A006F0068006E0073006F006E  1      0       2           Exact Match  johnson  0          Johnson & Johnson

, , Johnson Johnson, .

, , (_), -:

DECLARE @Term NVARCHAR(100);
SET @Term = N'"Johnson _ Johnson"';

SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 1);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 1);
GO

:

keyword                         group  phrase  occurrence  special      display  expansion  source
                                id     id                  term         term     type       term
0x006A006F0068006E0073006F006E  1      0       1           Exact Match  johnson  0          Johnson _ Johnson
0x005F                          1      0       2           Exact Match  _        0          Johnson _ Johnson
0x006A006F0068006E0073006F006E  1      0       3           Exact Match  johnson  0          Johnson _ Johnson

, , , "AT&T":

DECLARE @Term NVARCHAR(100);
SET @Term = N'"AT_T"';

SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 1);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 1);

:

keyword             group  phrase  occurrence  special      display  expansion  source
                    id     id                  term         term     type       term
0x00610074005F0074  1      0       1           Exact Match  at_t     0          AT_T
+2

More articles:


All Articles