CODEWITHSUNDEEP
sqloracle11g
user1614862
user1614862

Reputation: 4149

How to replace first two characters of a column with another value in oracle

I have a table in the following way.

TAX_ID_ID CUST_ID TAX_ID_TYP_CD TAX_ID_NO TAX_ID_DRV_UPCS_NO
1005004 2005004 ssn 100000006 100000006
1005006 2005006 ssn 100000007 100000007
1005009 2005009 ssn 100000008 100000008
1005012 2005012 ssn 100000009 100000009
1005014 2005014 ssn 100000010 100000010
1005017 2005017 ssn 100000011 100000011

I wanted to replace first two characters of TAX_ID_NO, TAX_ID_DRV_UPCS_NO with 25 in place of 10. The columns data type is varchar2. What would be the update query for this

Upvotes: 2

Views: 24842

Answers (2)

TechDo
TechDo

Reputation: 18629

Please try:

select 
  '25'||substr(TAX_ID_NO, 3), 
  '25'||substr(TAX_ID_DRV_UPCS_NO, 3) 
From YourTable;

Update your table using

update YourTable
set TAX_ID_NO='25'||substr(TAX_ID_NO, 2),
    TAX_ID_DRV_UPCS_NO='25'||substr(TAX_ID_DRV_UPCS_NO, 2);

Upvotes: 1

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

I think this will do your need.

Replace

Substring

String Concat

replace(TAX_ID_NO,TAX_ID_NO,'25'||substr( TAX_ID_NO, 3,length(TAX_ID_NO)-2))
replace(TAX_ID_DRV_UPCS_NO ,TAX_ID_DRV_UPCS_NO ,'25'||substr( TAX_ID_DRV_UPCS_NO , 3,length(TAX_ID_NO)-2))

Edit

Finally you have to Update the table as

UPDATE TABLE
   SET TAX_ID_NO = replace(TAX_ID_NO,TAX_ID_NO,'25'||substr( TAX_ID_NO, 3,length(TAX_ID_NO)-2)),
   SET TAX_ID_DRV_UPCS_NO = replace(TAX_ID_DRV_UPCS_NO ,TAX_ID_DRV_UPCS_NO ,'25'||substr( TAX_ID_DRV_UPCS_NO , 3,length(TAX_ID_NO)-2))

Upvotes: 2

Related Questions