I have a column containing:

Now I'm creating a table containing a case where I'm using the regexp_like

I need to use this function to match exact 2 substrings in a string i.e.

• when TL and A2 is present then Type 1
• when TL and A3 is present then Type 1
• When Tl and VG is present then Type 2
• When Tl and VF is present then Type 3

I have tried the following but did not work

USE Schema1; CREATE TABLE table1 (

Unit_Type varchar(200)
AS 
(case
      when REGEXP_LIKE (Unit_No,'^TL,','^A2') THEN "Type 1"
when REGEXP_LIKE (Unit_No,'^TL,','^A3') THEN "Type 1"
when REGEXP_LIKE (Unit_No,'^TL,','^VG') THEN "Type 2"
when REGEXP_LIKE (Unit_No,'^TL,','^VF') THEN "Type 3"
else Null
end);

So whenever I execute it executes properly and then when I try to load the data it gives the following error: "Error Code: 1210. Incorrect arguments to regexp_like 0.000 sec"

any idea how to resolve?

CodePudding user response：

REGEXP_LIKE only takes two arguments. You are passing three. You need to AND your conditions together

Also, the ^ means "beginning of the string" so your ^A3 will never match because it never is at the beginning of the string.

And you've got an extra comma after each of your ^TLs.

So try this:

when REGEXP_LIKE(Unit_No,'^TL') AND REGEXP_LIKE(Unit_No,'A3') THEN "Type 1"

Also, if you always have two digits after TL, then you can do

when REGEXP_LIKE(Unit_No,'^TL\d\d-A3') THEN "Type 1"

because \d is any single digit.