Regexextract in Custom column

Get Help
1

In order to prepare data for visualizing in Map, I need to re-format column with “city” name, because currently it contain number of city district like “Praha 1”. BTW, there are also correct strings like “Český Krumlov”. To remove district number, I created Custom column with FIELD FORMULA
=regexextract([CITY], " *[a-zA-Z-.áčďéěíňóřšťůúýžÁČĎÉĚÍŇÓŘŠŤŮÚÝŽ]+")
but preview or visualize fails with below message:

# There was a problem with your question
Most of the time this is caused by an invalid selection or bad input value. Double check your inputs and retry your query.

## Here’s the full error message
No method in multimethod ‘->honeysql’ for dispatch value: [:sqlserver :regex-match-first]

Is my regex bad ? Or where is problem ? My question is without filter, just Summarize - Count by “Custom column”

2

Hi @CZvacko
Well -. looks incorrect, but that error looks like the function doesn’t even work on SQL Server.
And I can see the driver knows that, but somehow the interface isn’t getting that information.
I don’t have an SQL Server right now to test with.

3

I have SQL Server management studio on hand (also visual studio for coding in c#…), I can run some command/test if you instruct me.

4

@CZvacko
Please post "Diagnostic Info" from Admin > Troubleshooting.
I cannot input regexextract on 0.35.4 without it showing an error input, so I don't understand how you even got past that, since you shouldn't be able to.

image
image568×719 28.9 KB

And like I said, the driver has regex options disabled:
https://github.com/metabase/metabase/blob/master/modules/drivers/sqlserver/src/metabase/driver/sqlserver.clj#L26

5

Info is below, at my place, no input error displayed. Webbrowser is Vivaldi 2.11.1811.47
When I setup such Custom column, Regexextract function wasn't offered by dropdown list with functions, but I remember that function, so copy/pasted it from documentation page. Just thought that only basic functions are offered. And documentation is wrong (Database limitations) doesn't mention Regexextract not supported by SQL Server.

regex
regex734×176 23.4 KB

{
"browser-info": {
"language": "cs-CZ",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.136 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "Cp1252",
"java.runtime.name": "Java(TM) SE Runtime Environment",
"java.runtime.version": "1.8.0_251-b08",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_251",
"java.vm.name": "Java HotSpot(TM) 64-Bit Server VM",
"java.vm.version": "25.251-b08",
"os.name": "Windows Server 2019",
"os.version": "10.0",
"user.language": "en",
"user.timezone": "Europe/Paris"
},
"metabase-info": {
"databases": [
"h2",
"sqlserver"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.35.3",
"date": "2020-04-21",
"branch": "release-0.35.x",
"hash": "1d424cb"
},
"settings": {
"report-timezone": null
}
}
}

6

@CZvacko Hmm… interesting. Can you test with 0.35.4 and with a different browser. Not sure where the problem is.

We probably need to go through each function for each database and update the limitation on the documentation, but generally if the function isn’t available in the dropdown, then it’s not available on that driver.

Check this issue and several of the referenced issues:
https://github.com/metabase/metabase/issues/12268 - upvote by clicking :+1: on the first post

7

@CZvacko As for translation, you can translate via the POEditor
https://poeditor.com/join/project/ynjQmwSsGh
And if you want to test a translation, then you can convert the PO-format to JSON-format (see the build scripts in ./bin/i18n/) and put the JSON-file in the JAR-file (it’s just a ZIP-archive, location frontend_client/app/locale/)

8

Tested with:

Edge 44.17763.831.0: no input error, can edit existing field formula and update it, can add same fomula into new column

IE 11.1217.17763.0: very strange in there,
in 1st attempt no input error, but in 2nd attempt with input error, can edit existing field formula but can’t update it (revert to original formula after press Update). Update & cancel button works sporadically (usually only if you make textbox with formula active). Copy/paste same formula into new column doesn’t work (pasted text disappear immediately).

9

@CZvacko

Okay, that’s two browsers that are almost reaching EOL.
I cannot reproduce with 0.35.4 querying a SQL Server table from Firefox and Chrome.

The sqlserver.metabase-driver.jar in the plugins directory should be from May 29th and with a SHA256 checksum of 6733fa6c0439c8ed40eb9a58b7dbb1cb22e53403a169867e7f8a9ceb9734ad87

Again, the function is not listed, so this is a minor problem.

What are your questions about translation?

10

My driver file is from April 21…
Sorry, tested with 0.35.3 :grimacing:, I thought I have same built, recently checked blog, but there was not new version mentioned…
After update, SHA256 match, but behavior is same inside Vivaldi.

11

@CZvacko
Releases are available here: https://github.com/metabase/metabase/releases
The blog will only be updated with major versions.

You can write comments in POEditor, but I don’t think they have a chat function. We don’t control how POEditor works or what functionality they provide. Think you’re the first person asking for this.
You’re welcome to open a topic here on the forum.

Guess the other question is not related to translation if it cannot be discussed here.

12

As workaround for regexextract, I modified SQL view like below (assuming district number is always at end and separated by space):
(CASE WHEN PatIndex('%[0-9]%', [CITYORIGINAL]) = 0 THEN [CITYORIGINAL] ELSE SubString([CITYORIGINAL],0 ,PatIndex('%[0-9]%', [CITYORIGINAL])-1) END) as CITY