How to use REGEXP in MySQL?

[failable]

After googling, I come up with this

        define_sql_function! {
            fn regexp(pattern: Text, val: Text) -> Bool;
        }

and use it like

let sql = diagnosis_dosage_admin::table
            .inner_join(drug_dosage_admin::table)
            .filter(
                drug_dosage_admin::id
                    .nullable()
                    .eq(diagnosis_dosage_admin::dosage_admin_id)
                    .and(
                        diagnosis_dosage_admin::diagnosis.eq_any(diagnoses).and(
                            drug_dosage_admin::generic_name
                                .eq(generic_name)
                                .and(regexp(pattern, drug_dosage_admin::specification)),
                        ),
                    ),
            );

But I get errors like this

[src/p/src/context/diesel.rs:401:9] &s = "SELECT `diagnosis_dosage_admin`.`id`, `diagnosis_dosage_admin`.`diagnosis_id`, `diagnosis_dosage_admin`.`diagnosis`, `diagnosis_dosage_admin`.`dosage_admin_id`, `diagnosis_dosage_admin`.`created_at`, `diagnosis_dosage_admin`.`created_by`, `diagnosis_dosage_admin`.`updated_at`, `diagnosis_dosage_admin`.`updated_by`, `drug_dosage_admin`.`id`, `drug_dosage_admin`.`drug_id`, `drug_dosage_admin`.`generic_name`, `drug_dosage_admin`.`dosage_admin`, `drug_dosage_admin`.`admin`, `drug_dosage_admin`.`specification`, `drug_dosage_admin`.`packing_unit`, `drug_dosage_admin`.`cycle`, `drug_dosage_admin`.`doses_per_cycle`, `drug_dosage_admin`.`dose`, `drug_dosage_admin`.`dose_unit`, `drug_dosage_admin`.`min_doses_per_cycle`, `drug_dosage_admin`.`max_doses_per_cycle`, `drug_dosage_admin`.`min_single_dose`, `drug_dosage_admin`.`max_single_dose`, `drug_dosage_admin`.`max_daily_doses`, `drug_dosage_admin`.`created_at`, `drug_dosage_admin`.`created_by`, `drug_dosage_admin`.`updated_at`, `drug_dosage_admin`.`updated_by` FROM (`diagnosis_dosage_admin` INNER JOIN `drug_dosage_admin` ON (`diagnosis_dosage_admin`.`dosage_admin_id` = `drug_dosage_admin`.`id`)) WHERE ((`drug_dosage_admin`.`id` = `diagnosis_dosage_admin`.`dosage_admin_id`) AND ((`diagnosis_dosage_admin`.`diagnosis` IN (?, ?)) AND ((`drug_dosage_admin`.`generic_name` = ?) AND regexp(?, `drug_dosage_admin`.`specification`)))) -- binds: [\"感冒\", \"头痛\", \"鼻舒适片\", \"^[0-9]+[片]$\"]"
thread 'main' panicked at src/saferx/src/main.rs:20:10:
called `Result::unwrap()` on an `Err` value: DieselRuleContextError(Query { source: DatabaseError(Unknown, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'regexp(?, `drug_dosage_admin`.`specification`))))' at line 1") })

What is the official to use REGEXP in diesel? Thanks.

[weiznich]

According to the mysql documentation is REGEXP an operator and not a function. That means using define_sql_function! won't work as that translates to a function call in the generated SQL.

Instead you likely want to use diesel::infix_operator! to define the corresponding operator.

[failable]

Thanks, that helps!