September 18, 2009 at 4:43 am
I have column with nvarchar(max) as data type.
i want to search a string in it which can present any where in the column
for ex '1234_2345_3454' and '1234_2345_1234'
if i search for 2345 it should return the rows.
contains function work fine when i do contains(col,'"2345*"') and 2345 is begining string.
where as if i do contains(col,'"*2345*"') it wont work
September 18, 2009 at 6:58 am
According to BOL, * is defined as:
prefix_term
Specifies a match of words or phrases beginning with the specified text. Enclose a prefix term in double quotation marks ("") and add an asterisk (*) before the ending quotation mark, so that all text starting with the simple term specified before the asterisk is matched. The clause should be specified this way: CONTAINS (column, '"text*"'). The asterisk matches zero, one, or more characters (of the root word or words in the word or phrase). If the text and asterisk are not delimited by double quotation marks, so the predicate reads CONTAINS (column, 'text*'), full-text search considers the asterisk as a character and searches for exact matches to text*. The full-text engine will not find words with the asterisk (*) character because word breakers typically ignore such characters.
When is a phrase, each word contained in the phrase is considered to be a separate prefix. Therefore, a query specifying a prefix term of "local wine*" matches any rows with the text of "local winery", "locally wined and dined", and so on.
It's not an all purpose wild card that can be used to find any text within a string.
The point of Full Text Search is that it splits text into individual words, the crux of the problem with your data is that your word breaker is an underscore rather than a space. If they were spaces, you could simply use contains(col,'"2345"'). I think it's possible to amend the word breakers manually, it might be worth looking into this.
How long is the average length of the nvarchar(max) field and how large is the table out of interest?
September 18, 2009 at 9:43 am
thanks for reply
i have used alternative for it by recursive function CTE i have got the result
actually i have a tree store in table as below
PARENT_ALKYCHILD_ALKY
1000070 1011372
1000070 1011268
1011268 1017162
1017162 1025131
1025131 1031954
1031954 1046002
1011372 1017276
1011372 1011388
1011388 1017168
1017168 1118317
1017276 1018271
in above example for record node these are child i have find all the child which value greater than equal to the parent i.e 100070 rest i have to skip.
for this i designed a cursor but for 2 million its taking lot of time . i am looking for optimal results
regards
Ramu
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply