I have a dataset which looks something like this:
| Category | Name |
|---|---|
| T-1029-PHONE | sjss |
| T-2629172-LAPTOP | ssus |
| T-26124-PC | udia |
| T-22 | |
| T-1029-PHONE | |
| T-1029 |
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:
| Category | Name |
|---|---|
| T-1029-PHONE | PHONE |
| T-2629172-LAPTOP | LAPTOP |
| T-26124-PC | PC |
| T-22 | |
| T-1029-PHONE | PHONE |
| T-1029 |
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?
>Solution :
update tech_table
set name = SUBSTR(Category, INSTR(Category, '-', 1,2)+1)
where category like '%-%-%'