Sfrgttk

Why are energy weapons seen as more acceptable in children's shows than guns that fire bullets?

Why did Tywin never remarry?

Is it possible to make a dynamically risizing filesystem-as-file?

How quickly could a motion be passed to alter minimum age for POTUS?

What happens when the last remaining players refuse to kill each other?

Is it appropriate to give a culturally-traditional gift to a female coworker?

Manager has noticed coworker's excessive breaks. Should I warn him?

Candle stand modeling question

This Website Needs More Cat Pictures?

Why Third *Reich*? Why is "reich" not translated when "Dritten" is? What is the English synonym of reich? Realm?

Someone wants me to use my credit card at a card-only gas/petrol pump in return for cash

How to explain one side of Super Earth is smoother than the other side?

How to make clear what a part-humanoid character looks like when they're quite common in their world?

Does human life have innate value over that of other animals?

Can "so" express a reason not a result?

How aware are characters of their class in-universe?

Are all aperiodic systems chaotic?

Would life expectancy increase if we replaced healthy organs with artificial ones?

Why does Python copy numpy arrays where the length of the dimensions are the same?

Why are recumbent bicycles and velomobiles illegal in UCI bicycle racing?

Found a major flaw in paper from home university – to which I would like to return

Sing Baby Shark

What does paperwork mean in this sentence?

Sci fi book, man buys a beat up spaceship and intervenes in a civil war on a planet and eventually becomes a space cop

Full Text Search only performs 'starts with' searches

Full Text Search With PostgreSQLcheck constraint that has dynamic listCombining full-text-search queriesFull-text search with german umlautsDoes full text search works with characters?Mysql might have too many indexesBest way to rank all the columns in a table and store those ranks in another tableSlow Full Text When using multiple full text searchesProblem with “not” in full text searchFull text search with substrings

3

Pretty basic question, but how can I get the containstable search to do an actual 'contains' search, rather than a 'starts with' one?

For example, from the examples in the documentation:

CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));  

CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);  

INSERT Flags VALUES ('France', 'Blue and White and Red');  

INSERT Flags VALUES ('Italy', 'Green and White and Red');  

INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');  

SELECT * FROM Flags;  

GO  



CREATE FULLTEXT CATALOG TestFTCat;  

CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;  

GO   



SELECT * FROM Flags;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green') ORDER BY RANK DESC;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green or Black') ORDER BY RANK DESC;  

Works fine, and so does:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"Gree*"') ORDER BY RANK DESC;  

But this does not:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen"') ORDER BY RANK DESC;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen*"') ORDER BY RANK DESC;

Should I be using a different query, or is what I'm looking for not possible?

sql-server full-text-search

share|improve this question

edited 18 hours ago

jadarnel27

5,29811836

asked 19 hours ago

PhilPhil

1606

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Fulltext search does not support endwith search
  
  – cuongle
  19 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  endswith or contains honestly. like if i wanted to search for 'danger', i couldn't find 'endangered'. Do you have documentation of that limitation anywhere?
  
  – Phil
  19 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It’s totally possible, just use LIKE keyword for postfix search
  
  – cuongle
  18 hours ago
  

  
  
  
  

add a comment | 

3

Pretty basic question, but how can I get the containstable search to do an actual 'contains' search, rather than a 'starts with' one?

For example, from the examples in the documentation:

CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));  

CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);  

INSERT Flags VALUES ('France', 'Blue and White and Red');  

INSERT Flags VALUES ('Italy', 'Green and White and Red');  

INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');  

SELECT * FROM Flags;  

GO  



CREATE FULLTEXT CATALOG TestFTCat;  

CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;  

GO   



SELECT * FROM Flags;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green') ORDER BY RANK DESC;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green or Black') ORDER BY RANK DESC;  

Works fine, and so does:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"Gree*"') ORDER BY RANK DESC;  

But this does not:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen"') ORDER BY RANK DESC;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen*"') ORDER BY RANK DESC;

Should I be using a different query, or is what I'm looking for not possible?

sql-server full-text-search

share|improve this question

edited 18 hours ago

jadarnel27

5,29811836

asked 19 hours ago

PhilPhil

1606

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Fulltext search does not support endwith search
  
  – cuongle
  19 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  endswith or contains honestly. like if i wanted to search for 'danger', i couldn't find 'endangered'. Do you have documentation of that limitation anywhere?
  
  – Phil
  19 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It’s totally possible, just use LIKE keyword for postfix search
  
  – cuongle
  18 hours ago
  

  
  
  
  

add a comment | 

Pretty basic question, but how can I get the containstable search to do an actual 'contains' search, rather than a 'starts with' one?

For example, from the examples in the documentation:

CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));  

CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);  

INSERT Flags VALUES ('France', 'Blue and White and Red');  

INSERT Flags VALUES ('Italy', 'Green and White and Red');  

INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');  

SELECT * FROM Flags;  

GO  



CREATE FULLTEXT CATALOG TestFTCat;  

CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;  

GO   



SELECT * FROM Flags;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green') ORDER BY RANK DESC;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green or Black') ORDER BY RANK DESC;  

Works fine, and so does:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"Gree*"') ORDER BY RANK DESC;  

But this does not:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen"') ORDER BY RANK DESC;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen*"') ORDER BY RANK DESC;

Should I be using a different query, or is what I'm looking for not possible?

sql-server full-text-search

share|improve this question

edited 18 hours ago

jadarnel27

5,29811836

asked 19 hours ago

PhilPhil

1606

CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));  

CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);  

INSERT Flags VALUES ('France', 'Blue and White and Red');  

INSERT Flags VALUES ('Italy', 'Green and White and Red');  

INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');  

SELECT * FROM Flags;  

GO  



CREATE FULLTEXT CATALOG TestFTCat;  

CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;  

GO   



SELECT * FROM Flags;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green') ORDER BY RANK DESC;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green or Black') ORDER BY RANK DESC;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"Gree*"') ORDER BY RANK DESC;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen"') ORDER BY RANK DESC;  

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen*"') ORDER BY RANK DESC;

share|improve this question

edited 18 hours ago

jadarnel27

5,29811836

asked 19 hours ago

PhilPhil

1606

share|improve this question

edited 18 hours ago

jadarnel27

5,29811836

asked 19 hours ago

PhilPhil

1606

edited 18 hours ago

jadarnel27

5,29811836

edited 18 hours ago

jadarnel27

5,29811836

asked 19 hours ago

PhilPhil

1606

asked 19 hours ago

PhilPhil

1606

1 Answer
1

active

oldest

votes

4

This is, I think, a common misconception when it comes to full text search in SQL Server.

Full Text Search enables searching for entire words as well as a number of other things. From the CONTAINS documentation

CONTAINS can search for:

• A word or phrase.




• The prefix of a word or phrase.




• A word near another word.




• A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).




• A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

So just like with normal indexes on text strings, you can do prefix searching.

Neither full text search, nor normal SQL Server indexes, support "postfix" or "contains" searching within a word or text string. So no leading wildcards, essentially.

Note that the same limitations apply to CONTAINSTABLE:

CONTAINSTABLE is useful for the same kinds of matches as the CONTAINS predicate and uses the same search conditions as CONTAINS.

I imagine these limitations exist because of the way indexes are logically stored in SQL Server - as b-trees, where it's very easy to "jump" or "seek" to the different parts of an index based on the start of the data in the indexed column.

share|improve this answer

edited 18 hours ago

answered 19 hours ago

jadarnel27jadarnel27

5,29811836

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230064%2ffull-text-search-only-performs-starts-with-searches%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

4

This is, I think, a common misconception when it comes to full text search in SQL Server.

Full Text Search enables searching for entire words as well as a number of other things. From the CONTAINS documentation

CONTAINS can search for:

• A word or phrase.




• The prefix of a word or phrase.




• A word near another word.




• A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).




• A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

So just like with normal indexes on text strings, you can do prefix searching.

Neither full text search, nor normal SQL Server indexes, support "postfix" or "contains" searching within a word or text string. So no leading wildcards, essentially.

Note that the same limitations apply to CONTAINSTABLE:

CONTAINSTABLE is useful for the same kinds of matches as the CONTAINS predicate and uses the same search conditions as CONTAINS.

I imagine these limitations exist because of the way indexes are logically stored in SQL Server - as b-trees, where it's very easy to "jump" or "seek" to the different parts of an index based on the start of the data in the indexed column.

share|improve this answer

edited 18 hours ago

answered 19 hours ago

jadarnel27jadarnel27

5,29811836

add a comment | 

4

This is, I think, a common misconception when it comes to full text search in SQL Server.

Full Text Search enables searching for entire words as well as a number of other things. From the CONTAINS documentation

CONTAINS can search for:

• A word or phrase.




• The prefix of a word or phrase.




• A word near another word.




• A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).




• A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

So just like with normal indexes on text strings, you can do prefix searching.

Neither full text search, nor normal SQL Server indexes, support "postfix" or "contains" searching within a word or text string. So no leading wildcards, essentially.

Note that the same limitations apply to CONTAINSTABLE:

CONTAINSTABLE is useful for the same kinds of matches as the CONTAINS predicate and uses the same search conditions as CONTAINS.

I imagine these limitations exist because of the way indexes are logically stored in SQL Server - as b-trees, where it's very easy to "jump" or "seek" to the different parts of an index based on the start of the data in the indexed column.

share|improve this answer

edited 18 hours ago

answered 19 hours ago

jadarnel27jadarnel27

5,29811836

add a comment | 

This is, I think, a common misconception when it comes to full text search in SQL Server.

Full Text Search enables searching for entire words as well as a number of other things. From the CONTAINS documentation

CONTAINS can search for:

• A word or phrase.




• The prefix of a word or phrase.




• A word near another word.




• A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).




• A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

So just like with normal indexes on text strings, you can do prefix searching.

Neither full text search, nor normal SQL Server indexes, support "postfix" or "contains" searching within a word or text string. So no leading wildcards, essentially.

Note that the same limitations apply to CONTAINSTABLE:

CONTAINSTABLE is useful for the same kinds of matches as the CONTAINS predicate and uses the same search conditions as CONTAINS.

I imagine these limitations exist because of the way indexes are logically stored in SQL Server - as b-trees, where it's very easy to "jump" or "seek" to the different parts of an index based on the start of the data in the indexed column.

share|improve this answer

edited 18 hours ago

answered 19 hours ago

jadarnel27jadarnel27

5,29811836

share|improve this answer

edited 18 hours ago

answered 19 hours ago

jadarnel27jadarnel27

5,29811836

answered 19 hours ago

jadarnel27jadarnel27

5,29811836

answered 19 hours ago

jadarnel27jadarnel27

5,29811836