Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>First off, you need to enabled Full text Searching indexing on the production servers, so if thats not in scope, your not going to want to go with this.</p> <p>However, if that's already ready to go, full text searching is relatively simple.</p> <p>T-SQL has 4 predicates used for full text search:</p> <ul> <li>FREETEXT</li> <li>FREETEXTTABLE</li> <li>CONTAINS</li> <li>CONTAINSTABLE</li> </ul> <p>FREETEXT is the simplest, and can be done like this:</p> <pre><code>SELECT UserName FROM Tbl_Users WHERE FREETEXT (UserName, 'bob' ) Results: JimBob Little Bobby Tables </code></pre> <p>FREETEXTTABLE works the same as FreeTEXT, except it returns the results as a table.</p> <p>The real power of T-SQL's full text search comes from the CONTAINS (and CONTAINSTABLE) predicate...This one is huge, so I'll just paste its usage in:</p> <pre><code>CONTAINS ( { column | * } , '&lt; contains_search_condition &gt;' ) &lt; contains_search_condition &gt; ::= { &lt; simple_term &gt; | &lt; prefix_term &gt; | &lt; generation_term &gt; | &lt; proximity_term &gt; | &lt; weighted_term &gt; } | { ( &lt; contains_search_condition &gt; ) { AND | AND NOT | OR } &lt; contains_search_condition &gt; [ ...n ] } &lt; simple_term &gt; ::= word | " phrase " &lt; prefix term &gt; ::= { "word * " | "phrase * " } &lt; generation_term &gt; ::= FORMSOF ( INFLECTIONAL , &lt; simple_term &gt; [ ,...n ] ) &lt; proximity_term &gt; ::= { &lt; simple_term &gt; | &lt; prefix_term &gt; } { { NEAR | ~ } { &lt; simple_term &gt; | &lt; prefix_term &gt; } } [ ...n ] &lt; weighted_term &gt; ::= ISABOUT ( { { &lt; simple_term &gt; | &lt; prefix_term &gt; | &lt; generation_term &gt; | &lt; proximity_term &gt; } [ WEIGHT ( weight_value ) ] } [ ,...n ] ) </code></pre> <p>This means you can write queries such as:</p> <pre><code>SELECT UserName FROM Tbl_Users WHERE CONTAINS(UserName, '"little*" NEAR tables') Results: Little Bobby Tables </code></pre> <p>Good luck :)</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload