How to Use SQL Queries to Search for Words in Google Sheets

If you end up analyzing large datasets that contain lots of text, being able to search for specific keywords will come in handy. In this tutorial, we will test various queries for finding matches and specific words in Google Sheets. Keep in mind that these conditions are case-sensitive, so ‘New York’ and ‘new york’ will yield different results.

“contains” condition

We have already used the “contains” condition in our tutorial for finding specific dates. This is a general condition and will look throughout every cell in the specified columns for cells that contain the matching keywords. Besides numbers, it can naturally look for certain keywords. For our example, we have added a table containing U.S. states’ etymology on one sheet.

U.S. state names etymology sheet. Data from Wikipedia article on U.S. state names etymology.

‍

We would like to list the states whose names come from the Spanish language. To do so, we identify column D as the column containing the language of origin and then construct the query command as follows:

select A, B, C, D, E where D contains 'Spanish'

Using this, we get the following result:

List of states whose names originated from the Spanish language: Arizona, California, Colorado, Florida, Montana, Nevada, New Mexico, Texas, and Utah. Queried from the source worksheet.

‍

Our search query managed to list all the states whose language of origin is Spanish. This command also works even if the word is buried inside the cell. As an example, we will run this query inside the QUERY function:

=QUERY(states!A1:F58,"select A, B, C, D, E, F where F contains 'George'")

And our result looks like this:

List of states whose description contains the word “George”: Georgia, Idaho, New Jersey, Oregon, and Washington. Queried from the source worksheet. — *We expected Georgia and Washington to show up as results but we are learning new tidbits about Idaho, New Jersey, and Oregon.*

‍

“starts with” and “ends with” condition

The “starts with” condition does a prefix match - matching the first few letters with what you have added as a keyword (or keyletters). As an example,

select A, B, C, D, E, F where A starts with 'Mi'

With this, we'll have to place it in Google Sheets' QUERY function with the format:

=query([range],"[SQL query]'")

Gives us:

List of states whose names start with “Mi”: Michigan, Minnesota, Mississippi, and Missouri. Queried from the source worksheet.

‍

On the other hand, the “ends with” condition does a suffix match - matching the last few letters with what you have added as a keyword (or keyletters). As an example,

select A, B, C, D, E, F where A ends with 'na'

With this, we'll have to place it in Google Sheets' QUERY function with the format:

=query([range],"[SQL query]'")

Gives us:

“matches” condition

The “matches” condition does a regular expression search. For example, this query

select A, B, C, D, E, F where A matches '.*ana'

With this, we'll have to place it in Google Sheets' QUERY function with the format:

=query([range],"[SQL query]'")

Gives us:

List of states whose names end with “ana” suffix pattern: Indiana, Louisiana, and Montana. Queried from the source worksheet.

“like” condition

The “like” condition admits two wildcards, allowing you to refine your search. The “%” wildcard serves as a placeholder for zero or more characters of any kind, while the “_” wildcard serves as a placeholder for one character, thus allowing you to specify the length of the expected word result. As an example, we will compare the two wildcards. This one

select A, B, C, D, E, F where A like 'India%'

With this, we'll have to place it in Google Sheets' QUERY function with the format:

=query([range],"[SQL query]'")

Will give this as a result:

List of states that starts with “India” followed by an indefinite number of characters: Indiana. Queried from the source worksheet.

While this one

select A, B, C, D, E, F where A like 'India_'

With this, we'll have to place it in Google Sheets' QUERY function with the format:

=query([range],"[SQL query]'")

Will give this as a result:

As you can see, the former query looked for words in column A that began with “India”, and listed them regardless of the amount of succeeding characters. The latter query looked for words in column A that began with “India” and then followed by a single character. Since no row included a word in column A that fulfilled such condition.

Combining two or more separate keywords

If you want to combine two or more separate keywords in the same query, there is a specific way of doing so. For our example, we want to list down U.S. state names that originated from either English or Spanish. To do so, we will write the following query:

select A, B, C, D, E where D contains 'Spanish' or D contains 'English'

With this, we'll have to place it in Google Sheets' QUERY function with the format:

=query([range],"[SQL query]'")

And this is the result that we will get:

List of states whose names originated from either Spanish language or English language: Arizona, California, Colorado, Delaware, Florida, Georgia, Maine, Maryland, Montana, Nevada, New Hampshire, New Mexico, New York, North Carolina, South Carolina, Texas, Utah, and Washington. Queried from the source worksheet.

There’s much more to add here…

...before we can start efficiently using this for our analysis. Keyword search is an important part and parcel of customer feedback analysis, which will give us important metrics such as satisfaction rate and net promoter score. A quick look through the principles behind keyword search will expose you to how complex and complicated natural language processing is.

Instead of studying an entirely new field just to gauge customer feedback, consider trying Lido. With a few clicks on your laptop, you can now access all the relevant metrics without going through the hassle of accessing the SQL databases of your eCommerce platforms and then coding the formulas to process them. Let our platform do it all for you!