How to check if a column value is contained within a string

Question

When a payment is received in my company's banking account, a webhook is triggered to our application, providing data related to the payment, including the payment label.

This label is a string written by the client and can vary significantly from one instance to another. We typically provide a reference to the client to include in the label, allowing us to identify it. The reference pattern conforms to this regular expression: /\A[a-zA-Z]{2,5}-?\d{4}-?\d{2}-?\d{4}\z/. For example: "ABCDE-2023-01-0001" or "ab-2023-01-0001" etc.

Additionally, I have an Invoice model with two columns: number and moneyin_key. The number value generally follows this format: ABCDE-2023-01-0001 and the moneyin_key value is the same but without the hyphens, like "ABCDE2023010001".

I want to create a scope to retrieve all the invoices related to the label provided by the client. As mentioned, the label can vary widely, but here's an example: "VIR. O/ JUMBO VISMA MOTIF: EDP2023100001" My scope should be structured as follows:

scope :related_to_payment_label, -> (label) { do_something_here }

I've tried several approaches, but none of them have been successful. Example:

scope :related_to_payment_label, -> (label) {
  where("number ILIKE :label OR moneyin_key ILIKE :label", label: "%#{label}%")
}

Any ideas?

Answer 1

You can use REGEXP function here like this:

scope :related_to_payment_label, -> {
  where("number REGEXP :regexp OR moneyin_key REGEXP :regexp", regexp: '[a-zA-Z]{2,5}-?\d{4}-?\d{2}-?\d{4}\z')
}

Or you can use ~* for case insensitive regexp and ~ for case sensitive if your database is Postgres

For instance:

scope :related_to_payment_label, -> {
  where("number ~* :regexp OR moneyin_key ~* :regexp", regexp: '[a-zA-Z]{2,5}-?\d{4}-?\d{2}-?\d{4}\z')
}