Updated March 8, 2023
Introduction to SQL Text Search
Text Search in SQL can be done using the functions available in SQL: PATINDEX, CHARINDEX and LIKE. Full-text is the searching facility that allows the users to search for certain keys that are not even mentioned perfectly and help in retrieving the searched filtered data in a fast and easy way. The full-text search is most often used by search engines such as Google or bing and also in blog post searching, e-commerce site search, etc. In SQL, the full-text search facility is provided to search for the matching text in one or more column data. In this article, we will see about the working of text search using LIKE function and full-text search and see how we can define full-text search in SQL with the help of example wherever possible.
Text Search Using PATINDEX, CHARINDEX and LIKE Functions
We can use the LIKE function to search for the words that begin with, end with, or contain certain characters that we have specified. Further, PATINDEX, CHARINDEX functions help us retrieve the string’s position that we are trying to search in other strings or character-based columns and returns the index if the string found successfully.
Let us consider the example by using a table named dictionary that contains the following records.
Code:
SELECT * FROM dictionary;Output:
Now, we have to search for the records that contain the “word” string in their column values of meaning. For this, we will use the following query containing the LIKE function.
Code:
SELECT * FROM dictionary WHERE meaning LIKE "%word%";The execution of the above query statement gives the following output containing two records which have “word” in their meaning,
Output:
Let us now use the CHARINDEX function to find out whether a particular string has a “hill” word in it and search for it. If the CHARINDEX function returns a non-zero positive value, then the key which we are trying to search has been found at the position that is retrieved from the function; else, the search string is absent.
Our example searches for the “hill” word in the string using the following query statement.
Code:
SELECT CHARINDEX('hill', '170, Mountain Villa, Prince Edward Parade, Cross Road, Hunters Hill');Output:
Let us now search for some other string in the same original string, say “well” using the CHARINDEX function and observe the output of the following query.
Code:
SELECT CHARINDEX('well', '170, Mountain Villa, Prince Edward Parade, Cross Road, Hunters Hill');The execution of the above query statement gives the following output with 0 as return position, which means that the word with value “well” was not found in the original string “170, Mountain Villa, Prince Edward Parade, Cross Road, Hunters Hill”.
Output:
Full-Text Search Working
- The full-text search technique makes the use of the entities of databases called documents that have textual data in them, such as the name of customers, items, descriptions, and then search based on keywords. For example, when a search such as Pant and Shirt is made, then the retrieved searched resultset includes the result containing pant word separately and result of shirt word separately and also the results that contain shirt and pant together in any of the order in it like shirt and pant or pant and shirt togetherly in the content.
- Even though the features like PATINDEX, CHARINDEX and LIKE the statement is available in SQL, they are not sufficient for the search when the column length is more, or the search is to be made on multiple column content. Using the full-text search provides many advantages such as search, flexibility, and ranking performance-based on relevance.
- The performance of PATINDEX, CHARINDEX, LIKE, and REPLACE used for searching is very less as all the contents of the table are needed to be searched. Also, the flexibility of the search provided by PATINDEX, CHARINDEX, and LIKE is not that effective. For example, when we have to find the resultset such that it will contain a shirt but not tie in, it is not possible. While full-text search uses sophisticated algorithms to search, that is done using a full-text index generated from the text data and lookup index of the table.
Features of Full-Text Search
- Some of the other features of full-text search in SQL include SQL interface, dynamic indexing, small index size, and high-speed search.
- Whenever any of the data of the columns on which the full-text index is created is changed, then the full-text index may or may not be updated in SQL.
- The updation of full-text search indexes is asynchronous and is called as a population of the index.
- The full-text index that is created to perform full-text searches is relatively small in size.
Creating Full-Text Index
The full-text index can be created on the columns of the table having character-based data types like char, XML, varbinary(max), nvarchar, FILESTREAM, ntext, image, varchar, or nchar and text. Remember that we can create only one full-text index per table in SQL. The full-text index can be defined on one or more columns. Before creating the full-text index for the table, it is required that the full-text catalog is present and created, which can be considered as a container or collection for full-text indexes of one or more tables.
We can use a catalog in SQL by using the following syntax.
CREATE FULLTEXT CATALOG name_of_catalogue;We can use the function FULLTEXTCATALOGPROPERTY available in SQL to retrieve the properties of the catalog in the format.
Code:
SELECT FULLTEXT CATALOG PROPERTY(name_of_catalog);Further, the full-text index can be created using the following syntax:
CREATE FULLTEXT INDEX ON name_of_table
[ ( { column_name
[ TYPE COLUMN data_type ]
[ LANGUAGE language_term ]
[ STATISTICAL_SEMANTICS ]
} [ ,...n]
) ]
KEY INDEX name_of_index
[ ON <catalog_option> ]
[ WITH [ ( ] <additional_options> [ ,...n] [ ) ] ]
[;]Conclusion
We can perform a text search in SQL by using available functions in SQL like PATINDEX, CHARINDEX, and LIKE. When huge data is involved and needs to search, the above functions fail in performance. This is where we can use full-text searches for text. We can define the full-text keys on only one column in a single table. All the full-text indexes are grouped in the same database in the form of one or more full-text catalogs. The population of the full-text search index can be requested by executing certain commands or scheduling the population, or even during the insertion of the records. Full-text search greatly speeds up the searching facility on the table contents in SQL and can be used easily.
Recommended Articles
We hope that this EDUCBA information on “SQL Text Search” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
