SQL query handling _ in the string to query.

Posted on 2007-10-19
Last Modified: 2012-06-27
There are 2 records in a database.

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 to be returned.
How can the SQL be written to solve this?
Question by:icongo
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    underscore is a wildcard also, and would need to be escaped:

    select * from newsletter_members where member_email like '%john[_]doe@%'
    LVL 92

    Accepted Solution


    where member_email like '%john[[_].]doe@%'
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    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@%'
    LVL 92

    Expert Comment

    by:Patrick Matthews

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


    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    I think I need to sleep now ;) Great catch Angel and matthew  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now