CODEWITHSUNDEEP
phpmysqlregex
imperium2335
imperium2335

Reputation: 24112

MySQL Regex ignore white space

In my database I have a number: 00 48 76 848

The string to match against is: 004876848

I have:

...
WHERE tel REGEXP '\s($q)'
...

But that doesn't work.

What regexp do I need to achieve this?

$q = preg_replace("/[\\D]+/", "", $q);
        return DB::select('SELECT contacts_phones.id, CONCAT(firstName, " ", lastName) AS name, tel, "phoneNumber" AS type, entities.name AS company,
            entities.id AS companyId
            FROM contacts_phones, contacts, entities
            WHERE tel REGEXP "[[:space:]]"
            AND contacts_phones.contactId = contacts.id
            AND entities.id = contacts.ownerId
            AND contacts.ownerTypeId = 1
            AND contacts.archived = 0
            LIMIT ' . $limit, array('%' . $q . '%'));

Upvotes: 0

Views: 1117

Answers (2)

Gerald Schneider
Gerald Schneider

Reputation: 17797

You can't tell regex just to "ignore" whitespace. You can only match it, which is next to impossible with a value like yours.

An easier approach, without regex, would be to remove the spaces with REPLACE() before you test it against your value:

 WHERE REPLACE(`tel`, ' ', '') = '004876848'

Good practice would be to normalize your values before you store them in the database. That makes everything else a lot easier.

Upvotes: 1

Severino Lorilla Jr.
Severino Lorilla Jr.

Reputation: 1637

WHERE tel REGEXP '[[:space:]]';

Upvotes: 0

Related Questions