Regexp expression from Oracle SQL to Big Query-CodePudding

I previously had help here for an Regexp expression in oracle sql which worked great.However, our place is converting to Big Query and the regexp does not seem to be working anymore.

In my tables, i have the following data

WC 12/10 change FC from 24 to 32
W/C 12/10 change fc from 401 to 340
W/C12/10 18-26

This oracle sql would have split the table up to give me the before number (24) and (32) and (12/10).

cast(REGEXP_SUBSTR(Line_Comment, '((\d  |\d )(change )?(- |-|to |to|too|too )(\d ))', 1, 1, 'i',2) as Int) as Before,
cast(REGEXP_SUBSTR(Line_Comment, '((\d  |\d )(change )?(- |-|to |to|too|too )(\d ))', 1, 1, 'i', 5) as Int) as After,
REGEXP_SUBSTR(Line_Comment, '((\d )(\/|-|.| )(\d )(\/|-|.| )(\d ))|(\d )(\/|-|.| )(\d )', 1, 1, 'i') as WC_Date,

Totally understand that comments are not consistent and may not work but if it works more than 80% of the time which it has then we are fine with this.

Since moving to big query, I'm getting this error message. In oracle, the tables were in varchar but in big query when they migrated it, its now in strings. Could this be the reason why its broken?Is there anyone who can help with this?This is way over my head.

No matching signature for function REGEXP_SUBSTR for argument types: STRING, STRING, INT64, INT64, STRING, INT64. Supported signatures: REGEXP_SUBSTR(STRING, STRING, [INT64], [INT64]); REGEXP_SUBSTR(BYTES, BYTES, [INT64], [INT64]) at [69:12]

CodePudding user response：

Since google bigquery