How do you use wildcards in SQL?

Wildcards in SQL are special characters used to search for patterns in text data within a database. They are primarily used with the LIKE operator in WHERE clauses to filter results based on partial matches. The two most common wildcards are % (percent sign) and _ (underscore). The % represents zero or more characters, while _ represents exactly one character. For example, WHERE name LIKE 'J%' finds all names starting with "J", and WHERE code LIKE 'A_1' matches values like “A21” or “AX1” where the second character can be anything. These wildcards enable flexible queries when exact matches aren’t possible or practical.

To use wildcards effectively, combine them with specific characters to narrow results. For instance, WHERE email LIKE '%@gmail.com' finds all Gmail addresses by matching any text before "@gmail.com". You can also use multiple wildcards in a single pattern: WHERE product_code LIKE 'T_SH__T%' might match “TEST_123” or "T_SH8T_2023". If you need to search for literal occurrences of wildcards (like an actual % in data), use the ESCAPE keyword. For example, WHERE note LIKE '%25%%' ESCAPE '!' uses ! as an escape character to find strings containing "%25%", where !% represents a literal %. Note that overusing wildcards, especially leading % (e.g., LIKE '%son'), can slow queries because indexes may not be used efficiently.

Wildcards are best suited for simple text patterns. For complex searches, consider full-text search features available in databases like MySQL or PostgreSQL. A practical example is filtering phone numbers: WHERE phone LIKE '(___) ___%' matches U.S.-style numbers like "(123) 456-7890". Always test patterns with edge cases, such as empty strings or special characters. Be mindful of case sensitivity, which depends on the database’s collation settings—LIKE 'a%' might behave differently in case-sensitive vs. case-insensitive environments. Avoid leading wildcards unless necessary, and use constraints or indexing strategies to optimize performance for frequently queried patterns.