Escaping % and _ in like SQL queries

[pboguslawski]

Describe the feature

Allow escaping % and _ in like SQL queries sent to database using ESCAPE clause as described on:

https://www.sqlitetutorial.net/sqlite-like/ https://mariadb.com/kb/en/like/ https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-LIKE

Motivation

According to https://github.com/go-gorm/gorm/issues/5828#issuecomment-1377642307 GORM doesn't allow searching using literal % nor _ in SQL like queries.

Related Issues

https://github.com/go-gorm/gorm/issues/5828

[a631807682]

According to https://github.com/go-gorm/gorm/issues/5828#issuecomment-1377642307 GORM doesn't allow searching using literal % nor _ in SQL like queries.

Gorm currently does not support Like api, but we support custom Clauses, you can implement it yourself, we will be happy if you are willing to create a package or pr for it.

Here is a simple implementation

  word := "%" 
  query := escapeLike("%", "%", word)

func escapeLike(left, right, word string) string {
    var n int
    for i := range word {
        if c := word[i]; c == '%' || c == '_' || c == '\\' {
            n++
        }
    }
    // No characters to escape.
    if n == 0 {
        return left + word + right
    }
    var b strings.Builder
    b.Grow(len(word) + n)
    for _, c := range word {
        if c == '%' || c == '_' || c == '\\' {
            b.WriteByte('\\')
        }
        b.WriteRune(c)
    }
    return left + b.String() + right
}

[kaiguan]

According to #5828 (comment) GORM doesn't allow searching using literal % nor _ in SQL like queries.

Gorm currently does not support Like api, but we support custom Clauses, you can implement it yourself, we will be happy if you are willing to create a package or pr for it.

Here is a simple implementation

  word := "%" 
  query := escapeLike("%", "%", word)

func escapeLike(left, right, word string) string {
    var n int
    for i := range word {
        if c := word[i]; c == '%' || c == '_' || c == '\\' {
            n++
        }
    }
    // No characters to escape.
    if n == 0 {
        return left + word + right
    }
    var b strings.Builder
    b.Grow(len(word) + n)
    for _, c := range word {
        if c == '%' || c == '_' || c == '\\' {
            b.WriteByte('\\')
        }
        b.WriteRune(c)
    }
    return left + b.String() + right
}

---

is not work . i think it need to use MySQL . like SELECT * FROM operation_log WHERE content LIKE '%\%' ESCAPE '\'; this is work. i have try many way,didn't work. mabey add a ESCAPE method is the best.