Select where like [ignore whitespace] (telephone # matching)

Posted on 2006-04-11
Last Modified: 2010-05-28
Hi Is there an easy way to write a select using like that ignores any whitespace.

Basically I  need to match telephone numbers that may or may not have been initially input with spaces included.

select * from customers where telephone like "123456"

would match fields where telephone = "123 456" or "123456" or " 123456" or "12 34 56"

Am I going to have to clean my data and force all user input ino this clean format or is there a select I can use on the data I have?

Many thanks,

Question by:malcx
    LVL 30

    Accepted Solution

    select * from customers where REPLACE(telephone, ' ', '') like "123456"
    LVL 3

    Author Comment

    Lovely thanks - exactlywhat was needed,


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now