CODEWITHSUNDEEP
phpmysqlsqlregex
Ilia Ross
Ilia Ross

Reputation: 13412

SQL query using regex with string containing dashes

I'm trying to compare the title, that comes from URL string containing dashes, e.g.:

Learning-Programming-Together-With-StackOverflow

with my DB record that has either:

Learning Programming Together With StackOverflow

or

Learning-Programming Together With StackOverflow

Now, I'm not sure how/which regex to use with SQL query, to match my DB records above. So, when I supply an URL string with dashes, what would SQL regex query would look like to match
my DB records?

I was thinking to try the following SQL query:

$sql = $this->db->conn->prepare("
    SELECT
      title
    FROM
      video
    WHERE
      title REGEXP :title 
    LIMIT 1
    ");
$sql->execute( array(
    ':title' => $title,
) );

but I am not sure at all how to combine it with regex /([-])/g that matches dashes?

Any help/advise please?

Upvotes: 0

Views: 585

Answers (2)

anubhava
anubhava

Reputation: 785286

You can apply preg_replace before supplying string to MySQL query:

':title' => preg_replace('/[ -]+/', '', $title),

And use this in your query:

REPLACE(REPLACE(title, ' ', ''), '-', '')

Basically idea is to remove all hyphens and spaces from both sides before comparing.

Upvotes: 1

kcsoft
kcsoft

Reputation: 2947

Why not

$sql = $this->db->conn->prepare("
    SELECT
      title
    FROM
      video
    WHERE
      REPLACE(title, ' ', '-') = REPLACE(:title, ' ', '-')
    LIMIT 1
    ");
$sql->execute( array(
    ':title' => $title,
    ) );

Or $title can have the replacement done in php.

Upvotes: 1

Related Questions