Grdb.swift: Question: How To use REGEXP in GRDB

[Mina-R-Meshriky]

Hi Groue,
I tried using sql : "SELECT * FROM table WHERE name REGEXP [ab]" but it was caught in the catch statement .. and I couldn't find anything about that in the documentation

Cheers,

[groue]

Hello @Mina-R-Meshriky

The more relevant information you give, the more support you can get - this is the the 101 of error reporting. So can you please provide a code snippet that helps reproduce your issue, the exact error you get, and what were your expectations?

[swiftlyfalling]

From the SQLite documentation:

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)".

(Emphasis mine.)

@Mina-R-Meshriky: Have you tried using GLOB instead?

[groue]

@swiftlyfalling is right: sqlite does not support the REGEXP operator out of the box. It needs help.

Here is some sample code which uses NSRegularExpression.

First define the regexp SQL function, as documented by SQLite:

let regexpFunction = DatabaseFunction("regexp", argumentCount: 2, pure: true) { (values) -> DatabaseValueConvertible? in
    guard
        let pattern = String.fromDatabaseValue(values[0]),
        let string = String.fromDatabaseValue(values[1])
        else { return nil }
    let reg = try NSRegularExpression(pattern: pattern, options: [])
    let match = reg.firstMatch(in: string, options: [], range: NSRange(location: 0, length: string.utf16.count))
    return (match != nil)
}

Then connect and register the function:

let dbQueue = try DatabaseQueue(...)
dbQueue.add(function: regexpFunction)

Now you can use the regexp function and the REGEXP operator:

try dbQueue.inDatabase { db in
    // true
    try Bool.fetchOne(
        db, "SELECT :string REGEXP :pattern",
        arguments: ["pattern": "l+", "string": "hello"])!

    // false
    try Bool.fetchOne(
        db, "SELECT regexp(:pattern, :string)",
        arguments: ["pattern": "l+", "string": "bye"])!
}

You can also extend the GRDB query interface:

func regexp(pattern: SQLExpressible, string: SQLExpressible) -> SQLExpression {
    return regexpFunction.apply(pattern, string)
}

class User : Record { ... }
let emailColumn = Column("email")
try dbQueue.inDatabase { db in
    let users = try User
        .filter(regexp(pattern: "@gmail.com$", string: emailColumn))
        .fetchAll(db)
}

I guess this closes this issue. Tell us, @Mina-R-Meshriky, if you have any more question.

[Mina-R-Meshriky]

Hi Groue,
Sorry that I didn't make My question Clear enough... but yes, what @swiftlyfalling reported from the SQLite Documentation was what I ask about.
I'll try your answer and get back to you .... but as far as I can see, your answer will solve my issue. 👍

thanks @swiftlyfalling for your contribution .. I'll also look at GLOB, as I don't know it.

[Mina-R-Meshriky]

yes your Solution Works Perfectly @groue and I can use now the REGEXP keyword in my sql statements,
@swiftlyfalling I looked up GLOB and it also solved my issue that needed REGEXP to solve

thanks for you both