Updating Existing Data In a Column

Toad for Oracle & DevOps Toolkit
1

Greetings!

I have a following table (ROOM) that I would like to update

UN_ID BLDG ROOM_NUMBER
8021 1782 105
8055 1803 007/008
8056 1803 M1-022
8057 1803 023
8058 1803 024
8059 1803 M1-101
8069 1803 M1-129
8070 1804 M3-001
8077 1804 M3-102

I want to remove all characters the preceds hyphen/dash (’-’) from ROOM_NUMBER, including hyphen/dash (’-’). Rows that do not have hyphen/dash (’-’) should be left as is. Rows with leading zeros should stay as such. After I run my update statement, my table should look like this

UN_ID BLDG ROOM_NUMBER
8021 1782 105
8055 1803 007/008
8056 1803 022
8057 1803 023
8058 1803 024
8059 1803 101
8069 1803 129
8070 1804 001
8077 1804 102

I was able to extract rows by removing hyphen/dash (’-’) and preceding data using the following script:

SELECT SUBSTR(ROOM_NUMBER, INSTR(ROOM_NUMBER, ‘-’)+1) ROOM_NUMBER

FROM ROOM

but I do not know how to use this select statement to update ROOM table.

I tried few different options using update statements but that did not work. One example is as follows:

UPDATE ROOM_DELETE A
SET A.ROOM_NUMBER. = (select B.ROOM_NUMBER from ROOM_DELETE B where B.ROOM_NUMBER = SUBSTR(B.ROOM_NUMBER, INSTR(B.ROOM_NUMBER, ‘-’)+1)
WHERE A.UNIQUEID. = (select B.UNIQUEID from ROOM_DELETE B where B.ROOM_NUMBER = SUBSTR(B.ROOM_NUMBER, INSTR(B.ROOM_NUMBER, ‘-’)+1)

Any assistance in resolving this issue would be appreciated.

2

Morning Jolly doe,

You are nearly there. You can simply do something like

Update room

Set room_number = SUBSTR(ROOM_NUMBER, INSTR(ROOM_NUMBER, ‘-’)+1)

Where room_number like ‘%-%’;

HTH

Cheers,

Norm. [TeamT]

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.

3

or for more general case uuse where

where REGEXP_LIKE (ROOM_NUMBER,’[A-Z][0-9]-*’)

4

Greetings Norm!

Thank you for your response. I tried your suggestion and it worked.

5

Greetings Damir Vadas!

Thank you for your response. I tried your suggestion and on a copied table & it worked.