Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL search for containing terms

Tags:

sql

contains

If it possible to search in a table for records of which its name contains a search term?

Thanks

like image 201
Lilz Avatar asked Apr 22 '10 14:04

Lilz


People also ask

How do you check the Contains in SQL?

The SQL CONTAINS function for Oracle database The basic syntax looks like this: CONTAINS ( column_name, substring, label ); The column_name and substring parameters are the same as they are with SQL Server. Column_name is the column you are searching and substring is the string you are searching for.

Is there a Contains function in SQL?

CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types. CONTAINS can search for: A word or phrase. The prefix of a word or phrase.

How do you check if a string contains a word in SQL?

Method 1 - Using CHARINDEX() function This function is used to search for a specific word or a substring in an overall string and returns its starting position of match. In case no word is found, then it will return 0 (zero).


2 Answers

SELECT * FROM `my_table` WHERE name LIKE '%my_search_term%'

or

SELECT * FROM `my_table` WHERE CONTAINS(name, 'search')

But be aware that the LIKE statement is very expensive. If you searching through a lot of text, you might want to consider using Sphinx for exemple.

like image 185
Mikushi Avatar answered Oct 13 '22 14:10

Mikushi


Sure. There is the CONTAINS predicate:

... WHERE CONTAINS(name, 'search-term')

There is also the LIKE operator and some DBMS allow for regular expressions.

like image 29
Joey Avatar answered Oct 13 '22 13:10

Joey