Use word boundary in Query with variable

Get Help
1

Hello,

I'm trying to use word boundaries regex in a PostgreSQL (Redshift) query. It works fine when I do my own query in my DB, but in Metabase, I can't manage to make it work with variable

Here the simplified query

SELECT *
FROM table
WHERE title ~* '(^|\\s)MyWord(\\s|$)'

In my Metabase query, I tried

title ~*  '(^|\\s)'{{word}}'(\\s|$)'

got ERROR: syntax error at or near "$1"

title ~*  '(^|\\s)'||{{word}}||'(\\s|$)'

got ERROR: operator is not unique: boolean[] || "unknown"

title ~* CONCAT( '(^|\\s)', {{word}}, '(\\s|$)')

got ERROR: function concat("unknown", "unknown", "unknown") does not exist

Do you know how can I achieve this in Metabase?
Thank you for helping

2

Can you share the result you are getting in your DB and the result you are getting in Metabase? In my case this works in both MEtabase and PostgreSQL

SELECT *
FROM table
WHERE title ~* '(^|\\s)MyWord(\\s|$)'

Also share the Admin -> Troubleshooting -> Diagnostic Info

3

Hi @TonyC, thanks for helping

WHERE title ~* '(^|\\s)MyWord(\\s|$)'

is also working well in both Metabase & my DB if I write it like that. But I need to have MyWord depending of a filter.
So when I replace it by a var name with {{ }}, it breaks

WHERE title ~* '(^|\\s){{ var_name }}(\\s|$)' (Or any of the solution shared in first message

Here's my diagnostic info:

{
  "browser-info": {
    "language": "fr",
    "platform": "Linux x86_64",
    "userAgent": "Mozilla/5.0 (X11; Linux x86_64; rv:108.0) Gecko/20100101 Firefox/108.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.16.1+1",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.16.1",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.16.1+1",
    "os.name": "Linux",
    "os.version": "4.14.290-217.505.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "Europe/Paris"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "redshift",
      "googleanalytics"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "13.7"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.4.1"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-09-02",
      "tag": "v0.44.3",
      "branch": "release-x.44.x",
      "hash": "7d50282"
    },
    "settings": {
      "report-timezone": null
    }
  }
}
4

I think the problem is with your regex cause the concat part works for me. Even if i apply regex

image
image1422×906 51 KB

5

Weird, I just tried same thing, and for me it's not working x')
image
image

6

@TonyC could custom column (created with custom expression) be a workaround for this problem?

7

@nemanja he can use custom expressions but not for his use case since he wants to dynamically add the appropriate word in the regex.

@AntoineYoo can you simply do a select statement with concat? Are you sure that is Redshift database you are connecting with? Since it's like the concat function just doesn't work

image
image1504×1026 42.1 KB