'~' char converted to '-' char in active record

[eluser]paris[/eluser]
hi all,

I have problem using '~' char with active record. I need to use '~' char in where clause for pattern matching. But in the sql string produced by active record, '~' char converted to '-' char.

here my sample model:

Code:

$this->db->select('*');
    $this->db->where('term ~*', 'regex');
    $query = $this->db->get('search');

    $result = $query->result_array();
    return $result;

the sql string should be like this:

Code:

SELECT * FROM 'search' WHERE 'term' ~* 'regex';

the sql string produced by active record :

Code:

SELECT * FROM 'search' WHERE 'term' -* 'regex';

anyone could help me?, what did I wrong?

thanks, paris.

[eluser]Mat-Moo[/eluser]
have you tried "FALSE" as the 3rd parameter? $this->db->where('term ~*', 'regex', FALSE);

[eluser]paris[/eluser]
thanks, mat for your reply...

using 3rd parameter solve the '~' char issue, but using FALSE option also eliminate the quotation marks char... when using FALSE option my SQL would be like :

Code:

SELECT * FROM search WHERE term ~* regex

witch the correct sql should be :

Code:

SELECT * FROM search WHERE term ~* 'regex'

any other ideas ?

[eluser]Mat-Moo[/eluser]

Code:

$this->db->where('term ~* "regex"',NULL,FALSE);

Any good?

[eluser]paris[/eluser]
hei Mat, i got following error from your suggestion :

Code:

A Database Error Occurred

Error Number:

ERROR: column "regex" does not exist LINE 3: WHERE term ~* "regex" ^

SELECT * FROM "search" WHERE term ~* "regex"

anyway... I have solve this issue using common string query instead of active record.
Thanks for your help Mat...

[eluser]Unknown[/eluser]
Thanks a lot!
Had the issue using common string query =(

[eluser]Unknown[/eluser]
$this->db->where('field RLIKE', "'^[0-9].*'", FALSE);