Skip to Main Content
  • Questions
  • I want to delete "_x000D_" string from many columns and more than 15000 rows

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Souheil.

Asked: March 11, 2021 - 8:14 pm UTC

Last updated: March 15, 2021 - 5:03 am UTC

Version: 20.2

Viewed 1000+ times

You Asked

Hi,

1- I've a table imported from .xls file containg more than 15000 rows and about 30 columns. Every sentence in a row ends with "_x000D_" word. I want to delete it or at least replace it with a blank character. I'm beginner. Please help me.
2- In the same table I've a customer id column containing varchar2 codes begining with letter P (P112113 for example). I want to delete the P in the begining of all codes and replace varchar2 to number type of this column. Thanks in advance.

and Chris said...

1 - You can REPLACE _x000D_ with nothing to remove it

2 - You could also REPLACE P, or use SUBSTR to return all the characters from the second onward.

To make customer_id a number, you'll need to add a column to do this:

create table t (
  cust_id varchar2(10),
  text    varchar2(1000)
);

insert into t 
  values ( 'P112113', 'This is a sentence_x000D_. This is another_x000D_.' );
  
alter table t
  add cust_id_num integer;
  
update t
set    text = replace ( text, '_x000D_' ),
       cust_id_num = substr ( cust_id, 2 );
       
select * from t;

CUST_ID   TEXT                                   CUST_ID_NUM   
P112113   This is a sentence. This is another.        112113 


Or - as you're changing most (all?) the rows in the table, you could use the create-table-as-select trick to update the data, then switch the tables over:

rollback;

select * from t;

CUST_ID   TEXT                                                 CUST_ID_NUM   
P112113   This is a sentence_x000D_. This is another_x000D_.        <null> 

create table tmp as 
  select cast ( substr ( cust_id, 2 ) as int ) cust_id, 
         replace ( text, '_x000D_' ) text
  from   t;
  
select * from tmp;

CUST_ID  TEXT                                   
 112113  This is a sentence. This is another.  

drop table t;
rename tmp to t;

select * from t;

CUST_ID  TEXT                                   
 112113  This is a sentence. This is another.  


This will be faster than UPDATE, but on 15,000 rows there will probably be little difference. You'll have to copy indexes, constraints, etc. from the old table to the new too. If this is a live application, you can do this online with dbms_redefinition.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you very much.

A reader, March 12, 2021 - 8:29 pm UTC


Connor McDonald
March 15, 2021 - 5:03 am UTC

glad we could help

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.