| Update a single character within string column [message #619698] |
Thu, 24 July 2014 10:48  |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
create table test_upd (col1 varchar2(50))
insert into test_upd values ('UMMCTA.M.ABC.STRING(0)');
insert into test_upd values ('UMMCTA.M.ABC.STRING1(0)');
insert into test_upd values ('UMMCTA.M.ABC.STTRING(1)');
insert into test_upd values ('UMMCTA.M.ABC.STRINGGGG(0)');
insert into test_upd values ('UMMCTA.M.ABC.STRING234(-1)');
insert into test_upd values ('UMMCTA.M.ABC.STRING(+1)');
select * from test_upd;
UMMCTA.M.ABC.STRING(0)
UMMCTA.M.ABC.STRING1(0)
UMMCTA.M.ABC.STTRING(1)
UMMCTA.M.ABC.STRINGGGG(0)
UMMCTA.M.ABC.STRING234(-1)
UMMCTA.M.ABC.STRING(+1)
Is it possible to update the table such that -
1. Replace the character T in the first word to P. i.e. UMMCTA to UMMPTA
2. Replace the number within bracket to 0. If it is +1 or 0, it should not be changed.
Don't just want to display the results - it has to be updated in the same table with a single update statement if possible.
After updating, the data in the table should look like.
UMMCPA.M.ABC.STRING(0)
UMMCPA.M.ABC.STRING1(0)
UMMCPA.M.ABC.STTRING(0)
UMMCPA.M.ABC.STRINGGGG(0)
UMMCPA.M.ABC.STRING234(0)
UMMCPA.M.ABC.STRING(+1)
Although, the table above is a sample and has only 1 column, i could define a unique key on my required table if that would help in the update.
Data Rules :
1. Total length of string will vary.
2. The word UMMCTA will always be at start and will always have the same set of letters. It has to be updated to UMMCPA
3. Number of "." will remain same, however length of data after the last "." will vary.
4. The number in bracket will always be single digit, however it may be preceded by "-" or "+". If it is (0) or (+1) it should stay the same, everything else should be changed to (0).
4. Except for the first word and last number, no other data in between will change.
|
|
|
|
|
|
|
|
|
|
| Re: Update a single character within string column [message #619704 is a reply to message #619702] |
Thu, 24 July 2014 11:28  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Michel, meanwhile, at my end I was curiously checking out the explain plan to see how much regular expressions would really cost in this case over simple substr and instr.
As you said, there MIGHT be a better solution than what you demonstrated, I have experienced that regular expressions are yet to prove their performance power over legacy methods till 11g. I kept hoping if 12c has anything to fancy it. But I have no clue yet.
By the way, did you Michel or anybody notice, OP is on Exadata. Now it makes me even more curious!
|
|
|
|
| Re: Update a single character within string column [message #619711 is a reply to message #619701] |
Thu, 24 July 2014 12:37 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Or simpler:
select col1,
replace(
regexp_replace(
col1,
'(\([^+].*\)$)|(\(\+[^1]\)$)',
'(0)'
),
'UMMCTA',
'UMMCPA'
) res
from test_upd
/
COL1 RES
-------------------------- -------------------------
UMMCTA.M.ABC.STRING(0) UMMCPA.M.ABC.STRING(0)
UMMCTA.M.ABC.STRING1(0) UMMCPA.M.ABC.STRING1(0)
UMMCTA.M.ABC.STTRING(1) UMMCPA.M.ABC.STTRING(0)
UMMCTA.M.ABC.STRINGGGG(0) UMMCPA.M.ABC.STRINGGGG(0)
UMMCTA.M.ABC.STRING234(-1) UMMCPA.M.ABC.STRING234(0)
UMMCTA.M.ABC.STRING(+1) UMMCPA.M.ABC.STRING(+1)
6 rows selected.
SQL>
SY.
|
|
|
|
| Re: Update a single character within string column [message #619733 is a reply to message #619700] |
Thu, 24 July 2014 14:15 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Sorry I had to head back home from work, couldn't reply earlier. Anyway, the below piece of code is all that i could come up with.
select substr(col1,1,4)||'P'||substr(col1,6)
from test_upd
I don't have the exact code since im at home now, but its something like the above piece. I knew there would be use of regular expression for the last part which is where i was stuck.
Looked at the regexp from Michel and Solomon - there is absolutely no way on earth I am even anywhere near understanding it, let alone coding it myself.
I am assuming my update would look like below. I would test it as soon as i get to work tomorrow.
UPDATE test_upd a
set a.col1= (select regexp(...) from test_upd b where a.col1=b.col1)
P.S.: Performance not an issue in this case, got less than 200 rows. If you want me to run some queries on the exadata machine, ill be glad to.
[Updated on: Thu, 24 July 2014 14:18] Report message to a moderator |
|
|
|
| Re: Update a single character within string column [message #619742 is a reply to message #619733] |
Thu, 24 July 2014 14:53 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
SQL> select *
2 from test_upd
3 /
COL1
--------------------------
UMMCTA.M.ABC.STRING(0)
UMMCTA.M.ABC.STRING1(0)
UMMCTA.M.ABC.STTRING(1)
UMMCTA.M.ABC.STRINGGGG(0)
UMMCTA.M.ABC.STRING234(-1)
UMMCTA.M.ABC.STRING(+1)
6 rows selected.
SQL> merge
2 into test_upd t
3 using (
4 select rowid rid,
5 replace(
6 regexp_replace(
7 col1,
8 '(\([^+].*\)$)|(\(\+[^1]\)$)',
9 '(0)'
10 ),
11 'UMMCTA',
12 'UMMCPA'
13 ) res
14 from test_upd
15 ) s
16 on (
17 t.rowid = s.rid
18 )
19 when matched
20 then
21 update
22 set col1 = res
23 /
6 rows merged.
SQL> select *
2 from test_upd
3 /
COL1
--------------------------
UMMCPA.M.ABC.STRING(0)
UMMCPA.M.ABC.STRING1(0)
UMMCPA.M.ABC.STTRING(0)
UMMCPA.M.ABC.STRINGGGG(0)
UMMCPA.M.ABC.STRING234(0)
UMMCPA.M.ABC.STRING(+1)
6 rows selected.
SQL>
SY.
|
|
|
|
| Re: Update a single character within string column [message #620038 is a reply to message #619742] |
Mon, 28 July 2014 04:34 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Thanks Michel and Solomon, the solutions worked perfectly !
Noticed that ROWID was used in the ON clause of the MERGE. I understand that this was necessary since i did not have any key on my table.
I had a small doubt on this, I was of the opinion that ROWID should not generally be used while writing SQL statements as it is something internal to oracle. Is there any merit to that statement? Also, would there be any impact (with respect to usage of rowid) if lets say the table in the example had millions of rows in it ?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
