CODEWITHSUNDEEP
sqloracle-database
Amzi
Amzi

Reputation: 399

How to remove the comma seperated part of the column record from the oracle table

We have the oracle table called "Names" which has approximately 10k records. The table has one of the column as "ID". Now this column is totally messed up and the records looks somewhat similar to the following.

ID
A.011
A.012
A.013
B.014
B.015
B.016
A-B-C
X_Y_Z

randomName
OtherName

Now, what we have decided is to remove the dot(.) seperated portion of the record and leave the other records as is for now. So the record should look like

ID
011
012
013
014
015
016
A-B-C
X_Y_Z
randomName
OtherName

Upvotes: 0

Views: 1142

Answers (4)

Ravi
Ravi

Reputation: 31407

Simply use following sql query, which will remove your . from the string.

UPDATE Names SET ID = regexp_replace(id,'\.+','');

Below query will remove all the character, which comes before .

UPDATE Names SET ID = substr(id,instr(id,'.')+1);

Upvotes: 0

Alexander
Alexander

Reputation: 3179

To UPDATE use this simple query:

UPDATE Name
   SET ID = SUBSTR(ID, INSTR(ID, '.')+1);

See SQL Fiddle

P.S. Although it's kinda weird, that SUBSTR(expr, 0) = SUBSTR(expr,1). But it works!

Upvotes: 0

wumpz
wumpz

Reputation: 9131

You could use this statement:

update names set id=regexp_replace(id,'^[^\.]+\.','')

as you see here, ids without . are simply untouched:

select regexp_replace('tst234','^[^\.]+\.','') from dual

and here a test with a .

select regexp_replace('A.0234','^[^\.]+\.','') from dual

Upvotes: 1

smnbbrv
smnbbrv

Reputation: 24541

use regular expression:

update names set id = case
                        when instr(id,'.') between 0 and length(id) - 1 then
                          regexp_replace(id,'^[^\.]*\.(.+)$','\1')
                        else id
                      end;

Upvotes: 0

Related Questions