Can this PDO Sql statement be simplified?

[John_Betong]

I am parsing an input text field and creating the following SQL statement which I was wondering if it could be simplifed. It works a treat by the way

   SELECT *
    FROM cities2 WHERE concat(name, " ", state_name)   LIKE ? 
    AND  concat(name, " ", state_name)  LIKE ?  
    AND concat(name, " ", state_name)   LIKE ? 

    Params:   array( "%as%",   "%ta%",   "%ba%" );

Output:

[DaveMaxwell]

You could try REGEXP

SELECT *
  FROM cities2
 WHERE concat(name, " ", state_name) REGEXP '(as|ta|ba)'

You’ll need to figure out how to do the case insensitive and global

http://dev.mysql.com/doc/refman/5.7/en/regexp.html

[Mittineague]

I looked a bit a REGEXP.

AFAIK MySQL is case insensitive - except maybe if the collation is “_cs_” and not “_ci_”

What scared me away from trying was

Warning

The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multibyte safe and may produce unexpected results with multibyte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.

But they should work against the transliteration field OK as long as the input isn’t multi-byte

[John_Betong]

@DaveMaxwell

You could try REGEXP

My knowledge of Regex is limited, I did search but was unable to find a solution as to how to join two strings with +, AND, & or &&.

I tried both REGEXP and RLIKE without success

Or works OK:

// Found: 8            
  $sql = 'SELECT *
          FROM cities2
          WHERE concat(name, " ", state_name) RLIKE "(zz)"';

// Found: 24            
  $sql = 'SELECT *
          FROM cities2
          WHERE concat(name, " ", state_name) RLIKE "(cam)"';

// Found: 32       
  $sql = 'SELECT *
          FROM cities2
          WHERE concat(name, " ", state_name) RLIKE "(zz|cam)"';

Desired Result
Pozzuoli | Campania

Online Test Results

[Mittineague]

@John_Betong

RLIKE

I’ve never used RLIKE - LIKE “%alphas%” works for me.
In MySQL the “%” is like the “.*” used in different languages

You may be experiencing some of the

may produce unexpected results with multibyte character sets

[DaveMaxwell]

Hmmmm. I’ve never done ANDs in regular expressions (I should have caught it, sorry).

Honestly, I don’t think you’ll get that big of a benefit out of changing over to a regular expression, But I could be wrong…