• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

SQL query handling _ in the string to query.

There are 2 records in a database.

john.doe@someone.ca
john_doe@someone.ca

When you run the following query.
select * from newsletter_members where member_email like '%john_doe@%'

2 records are returned, however, we only want the record with john_doe@someone.ca to be returned.
How can the SQL be written to solve this?
0
icongo
Asked:
icongo
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
underscore is a wildcard also, and would need to be escaped:

select * from newsletter_members where member_email like '%john[_]doe@%'
0
 
Patrick MatthewsCommented:
Try:

where member_email like '%john[[_].]doe@%'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
is that field only contains values of those fields or more text other than this? in the second case you neeed to check whether the first record contains a value with the '_' .

select * from newsletter_members where member_email like 'john_doe@%'
0
 
Patrick MatthewsCommented:
icongo,

Please ignore my post--I misread your question :)

Regards,

Patrick
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I think I need to sleep now ;) Great catch Angel and matthew  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now