I am working on a MYSQL database which is hosted on Google Cloud SQL. I am working through MySQL Workbench 8.0 CE.
I want to get a substring of the element_name column in the table p0999_pakbon in the schema projects.
Element names are like "LVLS 45 Beam 1" and "SPANO 18 Stud 1". I want to get: "LVLS 45" and "SPANO 18"
In an old copy of the database which is hosted on my local machine, the following code works perfectly:
SELECT REGEXP_SUBSTR(pp.element_name, "[A-Z]+[:space:][0-9]+")
FROM projects.p0999_pakbon pp
This is because REGEXP_SUBSTR is the standard function to get a substring in MySQL.
However, this code does not work in my database hosted in Google Cloud SQL. On this page https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#regexp_extract I seem to read that Google Cloud SQL uses REGEXP__EXTRACT instead. But also when I try the following:
SELECT REGEXP_EXTRACT(pp.element_name, "[A-Z]+[:space:][0-9]+")
FROM projects.p0999_pakbon pp
It does not work. It also doesn't work when I change the regex to a very simple one like "A".
I am getting these errors:
FUNCTION projects.REGEXP_SUBSTR does not exist
FUNCTION projects.REGEXP_EXTRACT does not exist
When I type the arguments in the wrong format, I get this error message:
Incorrect parameters in the call to stored fucntion 'REGEXP_EXTRACT'
Does anybody know how to solve my problem? Or maybe there is a workaround?
Assuming you just want the first two words, you could use SUBSTRING_INDEX here, which should work on most versions of MySQL:
SELECT SUBSTRING_INDEX('LVLS 45 Beam 1', ' ', 2) AS item
FROM yourTable;
If you also need to assert that the first term contains capitals, with the second digits, you could use REGEXP:
SELECT
CASE WHEN 'LVLS 45 Beam 1' REGEXP '^[A-Z]+ [0-9]+'
THEN SUBSTRING_INDEX('LVLS 45 Beam 1', ' ', 2)
ELSE 'no match' END AS item
FROM yourTable;