Is it possible to search for rows containing underscores in SQL Server?

Is it possible to do a SQL Server search for a row with particular strings containing an underscore? Learn how to structure the WHERE clause to search for such rows.

Is it possible to search for a row or rows with particular strings containing an underscore?

Suppose I have various email addresses all containing an underscore like gm_ace@where.com, hj_bus@ where.com, etc.How can I search for those particular emails?

If I use a WHERE clause such as 'WHERE eMail LIKE '%_' it returns all rows. 'WHERE eMail LIKE 'gm_%' returns rows that do not contain an underscore. Can you offer any suggestions?

The underscore character is a single character wildcard in the same way the percent sign is a multi-character wildcard. To query for an underscore character you need to wrap the underscore in square brackets:

WHERE email LIKE 'gm[_]%'

If you want to search for all email addresses with a two character value then an underscore, this WHERE clause would work:

WHERE email like '__[_]%@%'

This was first published in August 2007

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning



Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: