I have a dataset which looks something like this:

I want to update the 'Name' column by getting the last part of the 'Category' value string after the second '-' to get the table to look something like this:

I have already used this statement:

SELECT SUBSTR(Category, INSTR(Category, '-', 1,2) 1) AS Category 
FROM Tech_table;

This statement gets the last part of the string after the second dash however, I want to use a 'CASE WHEN' statement so that it will only look at the values in the 'Category' column where the format is like "%-%-%" such as 'T-1029-PHONE' and all the other ones in this same format, but not looking at the rows where the Category only has 1 dash such as 'T-1029'. Only when the format is like "%-%-%" in 'Category' then the 'Name' column values should update but when the Categories are not "%-%-%" then the 'Name' column for those Categories should not be updated but should remain the same.

Is there a way I can update my 'Name' column using a SELECT SUBSTR with a CASE WHEN statement for the scenario above?

CodePudding user response：

update tech_table
set name = SUBSTR(Category, INSTR(Category, '-', 1,2) 1)
where category like '%-%-%'