the contains prefix_term "06*" or simple term"06"returns a value where no word start with "06"

Question

I am using contains command of SQL Server to search a column named text(nvarchar(max)), the query returns a row where the text value having no word starting with "06", while it does have the word "06" in the middle of words. how it comes? the query is like this

select * from NCaseWildSearch  where  Contains(TEXT, '"06*" or "06"')

the value is something like this

<_C153_>1067</_C153_><_C154_>100010</_C154_><_C156_>5</_C156_><_C157_>INV-20210617-120</_C157_><_C162_>Jun 17 2021  2:46PM</_C162_><_C165_>Jul 26 2021  1:21PM</_C165_>

I didn't see any word start with "06", there is 20210617, but the "06" positioning in the middle; there do have a word begin with Jun, does the contains command recognize it as "06"? really have no idea of it. anyone can give me some tips? thanks

How did you set up the full text catalog? What [stop list](https://stackoverflow.com/a/25670070/11683) and [ffs parser](https://stackoverflow.com/a/48925918/11683) are you using? — GSerg, Oct 20 '22 at 19:27
Correct me if I'm wrong, but you want to return text columns that have a XML value starting with 06 in any of its tags? — griv, Oct 20 '22 at 19:48
Yes GRIV, I suppose the query will return a text in which there at least one word starting with "06", but I cannot find any word starting with "06" in the text . how the query return me with this text, I don't know — Jia Wan, Oct 20 '22 at 20:45
Its because its searching the entire column for `06`, not the individual XML tags as the starting point. — griv, Oct 20 '22 at 21:10
@GRIV You may be confusing [full text search](https://learn.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql) with [`charindex`](https://learn.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql). — GSerg, Oct 20 '22 at 23:06

score 0 · Answer 1 · answered Oct 21 '22 at 07:06

Searching through an XML as if it is just text is not always what you want. Because (for example) you are also searching through the node names.

A better approach might be to select the node values first:

WITH cte AS (
   SELECT CAST('<_C153_>1067</_C153_><_C154_>100010</_C154_><_C156_>5</_C156_><_C157_>INV-20210617-120</_C157_><_C162_>Jun 17 2021  2:46PM</_C162_><_C165_>Jul 26 2021  1:21PM</_C165_>' as xml) as xml
)
SELECT *
FROM (
   SELECT 
      T.v.query('.') as A,
      T.v.value('.','varchar(40)') as B
   FROM cte 
   CROSS APPLY cte.xml.nodes('//*') as T(v)
) x 
WHERE x.B LIKE '%06%'

output:

A	B
<C153>1067</C153>	1067
<C157>INV-20210617-120</C157>	INV-20210617-120

NOTE: This might be slower then directly searching for a text in a larger piece of text.

see: DBFIDDLE

the contains prefix_term "06*" or simple term"06"returns a value where no word start with "06"

1 Answers1