Split Postcode or any other string value

Get Help
1

Hi,

I just wanted to check if there is a Left function or something similar that we can use in order to 'truncate' a string? (i.e. we would find very useful to only use the first 3 letters of a postcode to group certain accounts, orders etc.)

I couldn't find a way with the usual SQL functions through Metabase.

Thanks!

2

Hi @ff9991
You can do anything you want in SQL - that is not parsed by Metabase.
For GUI questions there's Substring in Custom Expression:
https://www.metabase.com/docs/latest/users-guide/expressions.html

3

Hi flamber,

Thanks for your answer.

I thought I was doing that, but I can't understand why this is not working for me here and I am getting the following error.

image
image2798×1332 191 KB

Let me know if you have any hint here?

4

@ff9991 Post "Diagnostic Info" from Admin > Troubleshooting, and the database type you are querying.

5

Database is PostgreSQL, while the Diagnostic Info are below:

{
"browser-info": {
"language": "en-GB",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.114 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.10+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.10",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.10+9",
"os.name": "Linux",
"os.version": "4.14.209-160.339.amzn2.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"googleanalytics",
"postgres",
"redshift",
"mongo"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "12.5"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-03-17",
"tag": "v0.38.2",
"branch": "release-x.38.x",
"hash": "91f0ed6"
},
"settings": {
"report-timezone": "Europe/London"
}
}
}

6

@ff9991 But "accountId" is an integer column - the function expects a string (hence the name substring) so you would need to do something like this:
substring("accountId"::text, 0, 3)::integer as "accountId_test"

7

That makes sense and it is clear for Substring and Left function too.

I was used to other BI tools in which you don't need to explicitly parse the data field you are working, but that makes sense.

Thanks!

8

@ff9991
This has nothing to do with Metabase - the SQL is sent directly to Postgres, which gives the error.
Metabase has Custom Expressions in the GUI, which will (try to) not list incompatible column types for certain functions if there's a mismatch between the types.

9

Yes yes, I was referring to the fact that other BI tools have these in-built functions / Custom Expressions and there's no need to specify the data type, so I got a bit lost initially.

Thanks for the clarifications!