SQL Server Full-Text Search
Filter by Set

SQL Server Full-Text Search

...see more

SQL Server databases are full-text enabled by default. Before you can run full-text queries, however, you must create a full-text catalog and create a full-text index on the tables or indexed views you want to search.

Set up full-text search in two steps

There are two basic steps to set up a full-text search:

  1. Create a full-text catalog.
  2. Create a full-text index on tables or indexed view you want to search.

Each full-text index must belong to a full-text catalog. You can create a separate text catalog for each full-text index, or you can associate multiple full-text indexes with a given catalog. A full-text catalog is a virtual object and does not belong to any filegroup. The catalog is a logical concept that refers to a group of full-text indexes.

Get Started with Full-Text Search - SQL Server - Microsoft Docs

...see more

In most applications, we need to query or filter text that we have stored in a SQL Server database. If you have a Customer table and you need to filter by name it is ok to write a Select query with a Where clause to compare the input from the user with all customers’ names in that table.

Now, let’s say we have one table, a very simple one, to store all the posts from one blog.

We need to implement a functionality that allows the users to search for posts based on a specified input, but we need to show results even for posts similar to what the user-specified. The user doesn’t know the exact title of the post he is looking for, maybe he is not looking for any specific post but for some information related to what he entered.

Basic searches implementations, like the one we spoke about before to filter customers by name, use queries like these:

SELECT * FROM dbo.Post WHERE Title = 'param';
SELECT * FROM dbo.Post WHERE Title like '%param%';

Let’s say there is a post with the title: “The top 10 phones of 2017” and the user enter something like: “best mobiles” or “best smartphones in 2016”. Will the previous queries give us some good results? No, they won’t. They probably won’t get any result at all.

This kind of search is called Semantic Search and is very complicated to implement, at least from scratch. Fortunately, SQL Server provides a built-in feature that allows us to solve this problem and it is not so hard to get it working.

...see more

In order to start using SQL Server Full-Text Search (FTS) capabilities in our database, it’s only required to accomplish two steps:

  1. Create a full-text catalog.
  2. Create a full-text index with the columns we are going to enable FTS.

For our Post table, we want to allow FTS for the Title and Text columns. We need to assign a name to create the catalog.


USE BlogDb
GO
CREATE FULLTEXT CATALOG [PostFTSCatalog] WITH ACCENT_SENSITIVITY = ON
GO

Now we are ready to create the index. It is required that the table contains one unique index (normally we use the primary key). The table can have only one full-text index, to create the full-text index you specify the table, columns involved, the unique index on that table, and a name. This is the statement to create it:


CREATE UNIQUE INDEX UI_Post ON Post(Id);
GO

CREATE FULLTEXT INDEX ON Post
(Title, [Text])  
KEY INDEX UI_Post 
ON PostFTSCatalog
GO

That’s it, we have enabled SQL Server Full-Text Search in our table.

...see more

In order to search we use the built-in functions: contains, freetext, containstable and freetexttable.

Let’s see it in action.

Contains

SELECT Title, Text, * FROM [dbo].[Post] where contains(Title, 'phone')
SELECT Title, Text, * FROM [dbo].[Post] where contains(Title, '"ph*"')
SELECT Title, Text, * FROM [dbo].[Post] where contains(*, '"ph*" or "mobile"')
SELECT Title, Text, * FROM [dbo].[Post] where contains(*, '"phone" NEAR "best"')
SELECT Title, Text, * FROM [dbo].[Post] where contains(*, 'FORMSOF(INFLECTIONAL, buy)')

The query on line #1 will return posts where the word “phone” appears on the title. It’s very similar to a like clause but it will perform more efficient than a like. The query #2 will return rows where title contains words that start with “ph”, again very similar to like ‘ph%’.

Line #3 will return records where any of the columns in the full-text search index (in this case Title and Text) contains a word that starts with “ph” or contains the word “mobile”.

Query in line #4 returns records where the Title or Text columns has the word “phone”near the word “best”. Now things are getting better right? Let’s continue.

The last query will get us any row where the Title or Text columns contain the word “buy” in any tense, or in singular or plural in case of a noun. If there is a post that uses the words: buy or bought, it will return them.

Freetext

SELECT Title, Text, * FROM [dbo].[Post] where freetext(Title, 'phone')

Freetext will match the words with ‘phone’ by it’s meaning, not the exact word. It will also compare the inflectional forms of the words, similar to what the last query of the Contains did. In this case it will return any title that has ‘mobile’, ‘telephone’, ‘smartphone’, etc.

Containstable & Freetexttable

These functions return a table with values of relevance (RANK column) that indicate for each record, how well any word did match with what we are searching for. Freetexttable match by meaning and not only by word.

select p.Title, c.Rank, * from [dbo].[Post] as p inner join
    containstable(Post, Title, 'phone') as c
    on p.Id = c.[Key]
select p.Title, c.Rank, * from [dbo].[Post] as p inner join
    freetexttable(Post, Title, 'best') as c
    on p.Id = c.[Key]

This allows us to order by rank, so we get the best results first.

...see more

CONTAINSTABLE

Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.

Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.

For more information see: http://msdn.microsoft.com/en-us/library/ms189760(v=SQL.90).aspx 

FREETEXTTABLE

Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can be referenced in the FROM clause of a SELECT statement like a regular table name.

Queries using FREETEXTTABLE specify free text-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row. 

For more information see: http://msdn.microsoft.com/en-us/library/ms177652(v=SQL.90).aspx

Add to Set
  • .NET
  • .NET 6.0 Migration
  • 5 Best websites to read books online free with no downloads
  • 5 surprising things that men find unattractive
  • 5 Ways To Take Control of Overthinking
  • 6 simple methods for a more productive workday
  • 6 Ways To Stop Stressing About Things You Can't Control
  • Add React to ASP.NET Core
  • Adding reCAPTCHA to a .NET Core Web Site
  • Admin Accounts
  • Adobe Acrobat
  • Afraid of the new job? 7 positive tips against negative feelings
  • Agile
  • AKS and Kubernetes Commands (kubectl)
  • API Lifecycle Management
  • Application Insights
  • arc42
  • Article Writing Tools
  • Atlassian
  • Azure API Management
  • Azure App Registration
  • Azure Application Gateway
  • Azure Arc
  • Azure Arc Commands
  • Azure Architectures
  • Azure Bastion
  • Azure Bicep
  • Azure CLI Commands
  • Azure Cloud Products
  • Azure Cognitive Services
  • Azure Container Apps
  • Azure Cosmos DB
  • Azure Cosmos DB Commands
  • Azure Costs
  • Azure Daily
  • Azure Daily 2022
  • Azure Daily 2023
  • Azure Data Factory
  • Azure Database for MySQL
  • Azure Databricks
  • Azure Diagram Samples
  • Azure Durable Functions
  • Azure Firewall
  • Azure Functions
  • Azure Kubernetes Service (AKS)
  • Azure Landing Zone
  • Azure Log Analytics
  • Azure Logic Apps
  • Azure Maps
  • Azure Monitor
  • Azure News
  • Azure PowerShell Cmdlets
  • Azure PowerShell Login
  • Azure Private Link
  • Azure Purview
  • Azure Redis Cache
  • Azure Security Groups
  • Azure Sentinel
  • Azure Service Bus
  • Azure Service Bus Questions (FAQ)
  • Azure Services Abstract
  • Azure SQL
  • Azure Storage Account
  • Azure Tips and Tricks
  • Backlog Items
  • BASH Programming
  • Best LinkedIn Tips (Demo Test)
  • Best Practices for RESTful API
  • Bing Maps
  • Birthday Gift Ideas for Wife
  • Birthday Poems
  • Black Backgrounds and Wallpapers
  • Bootstrap Templates
  • Brave New World
  • Brian Tracy Quotes
  • Build Websites Resources
  • C# Development Issues
  • C# Programming Guide
  • Caching
  • Caching Patterns
  • Camping Trip Checklist
  • Canary Deployment
  • Careers of the Future You Should Know About
  • Cheap Vacation Ideas
  • Cloud Computing
  • Cloud Migration Methods
  • Cloud Native Applications
  • Cloud Service Models
  • Cloudflare
  • Code Snippets
  • Compelling Reasons Why Money Can’t Buy Happiness
  • Conditional Access
  • Configurations for Application Insights
  • Create a Routine
  • Create sitemap.xml in ASP.NET Core
  • Creative Writing: Exercises for creative texts
  • CSS Selectors Cheat Sheet
  • Cultivate a Growth Mindset
  • Cultivate a Growth Mindset by Stealing From Silicon Valley
  • Custom Script Extension for Windows
  • Daily Scrum (Meeting)
  • Dalai Lama Quotes
  • DataGridView
  • Decision Trees
  • Deployments in Azure
  • Dev Box
  • Development Flows
  • Docker
  • Don’t End a Meeting Without Doing These 3 Things
  • Drink More Water: This is How it Works
  • Dropdown Filter
  • Earl Nightingale Quotes
  • Easy Steps Towards Energy Efficiency
  • EF Core
  • Elon Musk
  • Elon Musk Companies
  • Employment
  • English
  • Escape Double Quotes in C#
  • Escaping characters in C#
  • Executing Raw SQL Queries using Entity Framework Core
  • Factors to Consider While Selecting the Best Earthmoving System
  • Feng Shui 101: How to Harmonize Your Home in the New Year
  • Flying Machines
  • Foods against cravings
  • Foods that cool you from the inside
  • Four Misconceptions About Drinking
  • Fox News
  • Free APIs
  • Funny Life Quotes
  • Generate Faces
  • Generate Random Numbers in C#
  • Genius Money Hacks for Massive Savings
  • GitHub
  • GitHub Concepts
  • Green Careers Set to Grow in the Next Decade
  • Habits Of Highly Stressed People and how to avoid them
  • Happy Birthday Wishes & Quotes
  • Helm Overview
  • How to Clean Floors – Tips & Tricks
  • How to invest during the 2021 pandemic
  • How To Make Money From Real Estate
  • How To Stop Drinking Coffee
  • Image for Websites
  • Inspirational Quotes
  • Iqra Technology, IT Services provider Company
  • Jobs Of 2050
  • jQuery
  • jQuery plugins
  • JSON for Linking Data (JSON-LD)
  • Json to C# Converters
  • Karen Lamb Quotes
  • Kubernetes Objects
  • Kubernetes Tools
  • Kusto Query Language
  • Lack of time at work? 5 simple tricks to help you avoid stress
  • Lambda (C#)
  • Last Minute Travel Tips
  • Last-Minute-Reisetipps
  • Latest Robotics
  • Leadership
  • List Of Hobbies And Interests
  • Logitech BRIO Webcam
  • Management
  • Mark Twain Quotes
  • Markdown
  • Meet Sophia
  • Message-Oriented Architecture
  • Microservices
  • Microsoft Authenticator App
  • Microsoft Power Automate
  • Microsoft SQL Server
  • Microsoft Teams
  • Mobile UI Frameworks
  • Motivation
  • Multilingual Applications
  • NBC News
  • NuGet
  • Objectives and Key Results (OKR)
  • Objectives and Key Results (OKR) Samples
  • OKR Software
  • Online JSON Viewer and Parser
  • Outlook Automation
  • PCMag
  • Phases of any relationship
  • Playwright
  • Popular cars per decade
  • Popular Quotes
  • PowerShell
  • PowerShell Array Guide
  • PowerShell Cmdlets
  • PowerShell Coding Samples
  • PowerToys
  • Prism
  • Pros & Cons Of Alternative Energy
  • Quill Rich Text Editor
  • Quotes
  • RACI Matrix
  • Razor Syntax
  • Reasons why singletasking is better than multitasking
  • Regular Expression (RegEx)
  • Resize Images in C#
  • RESTful APIs
  • Rich Text Editors
  • Rob Siltanen Quotes
  • Robots
  • Run sudo commands
  • Salesforce Offshore Support Services Providers
  • Sample Data
  • Save Money On Food
  • Score with authenticity in the job interview
  • Security
  • Semantic Versioning
  • Serialization using Thread Synchronization
  • Service Worker
  • Snipps
  • Speak and Presentation
  • SQL References
  • SQL Server Full-Text Search
  • Successful
  • Surface Lineup 2021
  • Surface Lineup 2021 Videos
  • SVG Online Editors
  • Team Manifesto
  • Technologies
  • Technology Abbreviations
  • Technology Glossary
  • TechSpot
  • That is why you should drink cucumber water every day
  • The Cache Tag Helper in ASP.NET Core
  • The Verge
  • Theodore Roosevelt Quotes
  • These 7 things make you unattractive
  • Things Successful People Do That Others Don’t
  • Things to Consider for a Great Birthday Party
  • Things to Consider When Designing A Website
  • Thoughts
  • TinyMCE Image Options
  • TinyMCE Toolbar Options
  • Tips for a Joyful Life
  • Tips for fewer emails at work
  • Tips for Making Better Decisions
  • Tips for Managing the Stress of Working at Home
  • Tips for Writing that Great Blog Post
  • Tips On Giving Flowers As Gifts
  • Tips you will listen better
  • Top Fitness Tips
  • Top Healthy Tips
  • Top Money Tips
  • Top Ten Jobs
  • Track Authenticated Users in Application Insights
  • Unicode Characters
  • Visual Studio 2022
  • Vital everyday work: tips for healthy work
  • Walking barefoot strengthens your immune system
  • Walt Disney Quotes
  • Ways for Kids to Make Money
  • Web Design Trends & Ideas
  • Web Icons
  • Web Scraping
  • Webhooks
  • Website Feature Development
  • What are my options for investing money?
  • What happens when you drink water in the morning
  • What Is Stressful About Working at Home
  • What To Eat For Lunch
  • Windows 11 Top Features You Should Know
  • Winston Churchill Quotes
  • XPath
  • You'll burn out your team with these 5 leadership mistakes
  • ZDNet
 
Sets