A neat SQL trick I learned today

I was working on some custom analysis of database tables and needed to query a username column for values that looked like this: 1234_CustomDescription

At first, I tried this:

SELECT [username] FROM [operators] WHERE [username] LIKE '%_%';

It returned every username in the table, which surprised me!

After a quick kagi, I found my answer: the _ character is a single-character wildcard match! This means it needs to be escaped when we use it, which is why our query was returning all records.

I switched away from Google Search and now use Kagi, hence the verb "kagi' to replace the verb "google."

• StackOverflow credit
• Microsoft documentation

Some useful? examples

In the Amtelco ecosystem, it's common for companies to adopt naming conventions for different aspects of the system. This type of query allows us to more cleanly query specific username, directory, and client account details.

use [intelligent];

-- Return all agents with an underscore (_) in their name
select * from agtagents where [name] like '%[_]%';

-- Return all agents with an username format like 1234_Description
select * from agtagents where [name] like '____[_]%';

-- Return all agents with a username format like D-Username/S-Username/etc
select * from agtagents where [name] like '_-%';

-- Return all clients with a clientnumber format 55XX (i.e., 5500 - 5599)
-- Notably, this ignores 55XXMoreStuff problems with '55%'
select * from cltClients where [clientnumber] like '55__';

Hopefully you find it useful.