LIKE in SQL, Explained for Beginners

In SQL, the LIKE operator allows you to perform pattern matching on columns containing text or lists. Unlike the strict equals operator (=), LIKE provides a more flexible way to search for values that contain a specific pattern within a larger string.

The LIKE operator uses two wildcard characters: % and _. The % wildcard matches any sequence of characters, including no characters at all. For example, %Shirt% would match any value containing the word "Shirt" anywhere within it, such as "Blue Cotton Shirt" or "Shirt Stay Clips". The _ wildcard matches exactly one character. For instance, S_m would match "Sam" or "Sum", but not "Steam".

When using LIKE, it's important to understand the meaning of the wildcards. % stands for any run of characters, including none, while _ requires exactly one character. For example, %at would match "cat", "hat", or "bat", but not "flat". To match multiple characters at the end of a value, you would use something like %at instead.

A LIKE pattern is written left to right, describing the pattern you're searching for. For example, %Cotton% reads as "anything, then Cotton, then anything". You can combine wildcards, as in A__-% which would match values starting with "A", followed by exactly two characters, a hyphen, and then anything.

It's worth noting that NOT LIKE can be used to find values that do NOT match a certain pattern. For instance, NOT LIKE %Shirt% would return all rows where the name does not contain the word "Shirt".

When using LIKE, case sensitivity can vary depending on the database. Some databases, like SQLite, are case-insensitive by default for A-Z letters. Others, like PostgreSQL, are case-sensitive and require ILIKE for case-insensitive matching. SQL Server and MySQL may also have database-specific collations that affect case sensitivity.

One common pitfall when using LIKE is the over-matching trap. For example, searching for LIKE %Video% in a Steam games dataset would match both "Video Production" and "360 Video" because both contain the letters "Video". To avoid this, you can specify more precise patterns, such as %, Sales% to match only values containing the word "Sales" surrounded by commas.

In some cases, LIKE can be used to search for values within a list stored in a single column. However, this approach may return unintended results since LIKE does not consider word boundaries. For example, LIKE %man% would match "man", "manage", "human", and "Germany".

To properly search for values within a list column, it's recommended to use separate columns for each value or employ alternative techniques like string splitting functions, depending on the database system being used.