Prefix searching

The full text search feature allows you to search for the beginning portion of a term. This is called a prefix search. To perform a prefix search, you specify the prefix you want to search for, followed by an asterisk. This is called a prefix term.

Keywords for the CONTAINS clause cannot be used for prefix searching unless they are in a phrase. For a list of CONTAINS keywords, see CONTAINS search condition.

You also can specify multiple prefix terms in a query string, including within phrases (for example, '"shi* fab"').

For complete syntax restrictions for prefix searching, see Allowed syntax for asterisk (*).

The following example queries the MarketingInformation table for items that start with the prefix shi:

SELECT ID, ct.score, Description
   FROM MarketingInformation CONTAINS ( MarketingInformation.Description, 'shi*' ) AS ct
   ORDER BY ct.score DESC;
ID score Description
906 2.295363835537917 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Visor</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown cotton construction. Shields against sun and precipitation. Metallic ions in the fibers inhibit bacterial growth, and help neutralize odor.</span></p></body></html>
901 1.6883275743936228 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>We've improved the design of this perennial favorite. A sleek and technical shirt built for the trail, track, or sidewalk. UPF rating of 50+.</span></p></body></html>
903 1.6336529491832605 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A sporty, casual shirt made of recycled water bottles. It will serve you equally well on trails or around town. The fabric has a wicking finish to pull perspiration away from your skin.</span></p></body></html>
902 1.6181703448678983 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>This simple, sleek, and lightweight technical shirt is designed for high-intensity workouts in hot and humid weather. The recycled polyester fabric is gentle on the earth and soft against your skin.</span></p></body></html>

ID 906 has the highest score because the term shield occurs less frequently than shirt in the text index.

 Prefix searches on GENERIC text indexes
 Prefix searches on NGRAM text indexes
 See also