CODEWITHSUNDEEP
regexnotepad++
Mistu4u
Mistu4u

Reputation: 5416

Regular expression to change decimal operator from comma to dot?

I have a bunch of insert queries in one text file retrieved from, let's say Oracle DB1. Now I have to execute the insert SQLs in another database,let's say Oracle DB2. Now the problem is the decimal separator in DB1 was "," but in DB2, it is ".". So I am getting error in DB2 during insertion as "invalid number". How can I change the decimal separator from "," to "." in notepad++. Following are 2 example of the insert queries.

Insert into schema1.tab1 (uniq_identifier,branch,INT_TYPE,NOM_RATE,EFF_RATE,FACTOR,START_DATE,END_DATE,BOOK_AMT,BOOK_FLG,LAST_BOOKED_DATE,INT_PAID_FLG,INT_PAID_AMT,WTAX_AMT,INT_PAYMENT_DATE,END_FLG,REVERSE_FLG,entity_id,create_user_id,creation_time,modified_user_id,modif_time,INT_PAID_TRAN_ID) values ('55000116754','BC000088','G','1.961','1.98','0,000054',to_date('22/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),to_date('04/07/17 00:00:00','DD/MM/RR HH24:mi:ss'),'1252,75','N',to_date('27/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),'Y','1252,73','50,11',null,'Y','N','1000','FINADM',to_date('21/06/17 20:49:50','DD/MM/RR HH24:mi:ss'),'user1',to_date('27/06/17 22:01:20','DD/MM/RR HH24:mi:ss'),null);

Insert into schema1.tab1 (uniq_identifier,branch,INT_TYPE,NOM_RATE,EFF_RATE,FACTOR,START_DATE,END_DATE,BOOK_AMT,BOOK_FLG,LAST_BOOKED_DATE,INT_PAID_FLG,INT_PAID_AMT,WTAX_AMT,INT_PAYMENT_DATE,END_FLG,REVERSE_FLG,entity_id,create_user_id,creation_time,modified_user_id,modif_time,INT_PAID_TRAN_ID) values ('55000065422','BC000008','G','1.961','1.98','0,000054',to_date('20/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),to_date('29/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),'4586,34','N',to_date('27/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),'Y','4586,36','183,45',null,'Y','N','1000','user1',to_date('18/06/17 08:03:49','DD/MM/RR HH24:mi:ss'),'user1',to_date('27/06/17 21:59:42','DD/MM/RR HH24:mi:ss'),null);

Now if you see the queries, there are two amount fields with comma. In first query we have '1252,73','50,11' and in the second query, we have '4586,36','183,45'.

Upvotes: 0

Views: 149

Answers (3)

Yaroslav Shabalin
Yaroslav Shabalin

Reputation: 1644

Actually you don't need to change anything in insert statements, since decimal separator is session specific parameter in Oracle and can be easily changed (i.e. see this question on Ask Tom). Change NLS_NUMERIC_CHARACTERS session parameter before executing the file (place following line at the very beginning of the file):

alter session set NLS_NUMERIC_CHARACTERS = ',.';

Upvotes: 0

Toto
Toto

Reputation: 91518

  • Ctrl+H
  • Find what: \d\K,(?=\d)
  • Replace with: .
  • check Wrap around
  • check Regular expression
  • Replace all

Explanation:

\d      : a digit
\K      : forget all we have seen until this position
,       : a comma
(?=     : lookahead, zerolength asszrertion, make sure we have:
  \d    : a digit
)       : end lookahead

Result for given example:

Insert into schema1.tab1 (uniq_identifier,branch,INT_TYPE,NOM_RATE,EFF_RATE,FACTOR,START_DATE,END_DATE,BOOK_AMT,BOOK_FLG,LAST_BOOKED_DATE,INT_PAID_FLG,INT_PAID_AMT,WTAX_AMT,INT_PAYMENT_DATE,END_FLG,REVERSE_FLG,entity_id,create_user_id,creation_time,modified_user_id,modif_time,INT_PAID_TRAN_ID) values ('55000116754','BC000088','G','1.961','1.98','0.000054',to_date('22/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),to_date('04/07/17 00:00:00','DD/MM/RR HH24:mi:ss'),'1252.75','N',to_date('27/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),'Y','1252.73','50.11',null,'Y','N','1000','FINADM',to_date('21/06/17 20:49:50','DD/MM/RR HH24:mi:ss'),'user1',to_date('27/06/17 22:01:20','DD/MM/RR HH24:mi:ss'),null);

Insert into schema1.tab1 (uniq_identifier,branch,INT_TYPE,NOM_RATE,EFF_RATE,FACTOR,START_DATE,END_DATE,BOOK_AMT,BOOK_FLG,LAST_BOOKED_DATE,INT_PAID_FLG,INT_PAID_AMT,WTAX_AMT,INT_PAYMENT_DATE,END_FLG,REVERSE_FLG,entity_id,create_user_id,creation_time,modified_user_id,modif_time,INT_PAID_TRAN_ID) values ('55000065422','BC000008','G','1.961','1.98','0.000054',to_date('20/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),to_date('29/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),'4586.34','N',to_date('27/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),'Y','4586.36','183.45',null,'Y','N','1000','user1',to_date('18/06/17 08:03:49','DD/MM/RR HH24:mi:ss'),'user1',to_date('27/06/17 21:59:42','DD/MM/RR HH24:mi:ss'),null);

Upvotes: 0

Zenoo
Zenoo

Reputation: 12880

Search for (\d),(\d).

Replace by $1.$2.

Here's a Javascript demonstration :

var str = `Insert into schema1.tab1 (uniq_identifier,branch,INT_TYPE,NOM_RATE,EFF_RATE,FACTOR,START_DATE,END_DATE,BOOK_AMT,BOOK_FLG,LAST_BOOKED_DATE,INT_PAID_FLG,INT_PAID_AMT,WTAX_AMT,INT_PAYMENT_DATE,END_FLG,REVERSE_FLG,entity_id,create_user_id,creation_time,modified_user_id,modif_time,INT_PAID_TRAN_ID) values ('55000116754','BC000088','G','1.961','1.98','0,000054',to_date('22/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),to_date('04/07/17 00:00:00','DD/MM/RR HH24:mi:ss'),'1252,75','N',to_date('27/06/17 00:00:00','DD/MM/RR HH24:mi:ss'),'Y','1252,73','50,11',null,'Y','N','1000','FINADM',to_date('21/06/17 20:49:50','DD/MM/RR HH24:mi:ss'),'user1',to_date('27/06/17 22:01:20','DD/MM/RR HH24:mi:ss'),null);`;

console.log(str.replace(/(\d),(\d)/g,'$1.$2'));

Upvotes: 1

Related Questions