How to Use the SQL Contains Command

There are many SQL statements and functions to query your database and retrieve or figure out useful information. One such function is the CONTAINS() function. This particular statement is not a standard SQL function – depending on which database you use, the CONTAINS() function operates differently. In this article, we talk about the CONTAINS() function as it relates to SQL Server and touch upon how it differs when you use another database.

Basic Usage and Syntax

At its core, the CONTAINS() function takes one substring and does a search to see if it’s in another string. For Microsoft SQL Server, CONTAINS() allows for a full text pattern match SQL search queries on your table. It returns a boolean value that indicates whether the function is truthy or falsy. 

SELECT <columnName> FROM <yourTable> WHERE CONTAINS (<columnName>, '<yourSubstring>');

All of the places in between a set of <> is your customized naming. Notice your substring is in between a set of quotes. The column names are the variable names you set your column names to when you created your table. 

CREATE TABLE MyTable
    ([name] varchar(26), [occupation] varchar(57))
;

INSERT INTO MyTable
    ([name], [occupation])
VALUES
    ('User 1', 'Web Developer'),
    ('User 2', 'Musician'),
    ('User 3', 'Doctor')
;

SELECT name, occupation FROM myTable WHERE CONTAINS (name, 'User');

If you have your table set up to be full-text indexed, then you can run the query above. The search looks for the pattern of the substring in its entirety – it does not look for misspellings or synonyms.

If you are unsure of how to set up your database to be full-text indexed, check the SQL LIKE clause. It’s similar to CONTAINS, but easier to set up out of the box.

SELECT
  name,
  occupation
FROM
  MyTable
WHERE name LIKE 'User%';

The % after the string ‘User’ indicates anything after ‘User’. So this code will return:

Other databases use the CONTAINS function in different ways – it doesn’t necessarily mean the same in SQL Server, for instance, that it means in PostgreSQL. 

For more information please check out your respective database’s documentation. For more information about the LIKE clause, please take a look at this blog post James Gallagher wrote about it on Career Karma.