I have a table which looks like this:
| Category | Name |
|---|---|
| T – 1029 – PHONE | sjss |
| T-2629172-LAPTOP | ssus |
| T-26124-PC | udia |
| T-22 | |
| T – 1029 – PHONE |
I am using this statement to update the ‘Name’ column in my table by getting the entire string after the second dash:
update tech_table
set name = SUBSTR(Category, INSTR(Category, '-', 1,2)+1)
where category like '%-%-%'
This is the result of running the statement:
| Category | Name |
|---|---|
| T – 1029 – PHONE | ‘ ‘PHONE |
| T-2629172-LAPTOP | LAPTOP |
| T-26124-PC | PC |
| T-22 | |
| T – 1029 – PHONE | ‘ ‘PHONE |
‘ ‘PHONE -> The ‘ ‘ before PHONE just shows that there is a space character before the PHONE value in the ‘Name’ column after the data is updated into this column. The space appears because my statement is taking all the characters after the second ‘-‘ in the ‘Category’ field including the space character.
Is there something I can include in my update statement to remove the space in front of the updated ‘Name’ value so that the table will look like this instead
| Category | Name |
|---|---|
| T – 1029 – PHONE | PHONE |
| T-2629172-LAPTOP | LAPTOP |
| T-26124-PC | PC |
| T-22 | |
| T – 1029 – PHONE | PHONE |
Im trying to eliminate the space before the ‘PHONE’ value without changing the ‘Category’ column.
I know there is a replace function such as REPLACE(ColumnValue, ‘ ‘,”) to remove spaces but wasnt sure how to include it in my update statement above.
>Solution :
You can do something like TRIM() function in oracle:
Orig: set name = SUBSTR(Category, INSTR(Category, '-', 1,2)+1)
New: set name = TRIM(SUBSTR(Category, INSTR(Category, '-', 1,2)+1))
Reference: https://www.oracletutorial.com/oracle-string-functions/oracle-trim/