Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • 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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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