Replace first two characters with three characters SQL Oracle-CodePudding

I have a question

I want to join two tables (Table1 and Table2) on custID column.

However for the join to work I need to edit Table1s custID column vlaues by removing the first two characters ('CC') and replacing them with 0s so the final output is padded to 8 digits.

So if Table1 had a value in custID of CC34054 then this would need to be converted to 00034054 for the join to identify that value in Table2.custID. If for instance the custID value in Table1 was CC3356, the value would need to be revised to 00003356 for the join to match.

Ive made some tables below so I can illustrate what I mean.

Table1

CustID
CC34054
CC3356
CC87901

Table2

CustID
00034054
00003356
00087901

I hope this makes sense. thanks!

CodePudding user response：

One option is to replace CC with an empty string and apply LPAD function to fill up to 8 characters with zeros; do it either in JOIN or - possibly - by updating table1 (so join would then look simpler, just on a.custid = b.custid).

Sample data:

SQL> with
  2  table1 (custid, name) as
  3    (select 'CC34054', 'Little' from dual union all
  4     select 'CC3356' , 'Scott'  from dual
  5    ),
  6  table2 (custid, surname) as
  7    (select '00034054', 'Foot'  from dual union all
  8     select '00003356', 'Tiger' from dual
  9    )

Query:

 10  select b.custid, a.name, b.surname
 11  from table1 a join table2 b on
 12    lpad(replace(a.custid, 'CC', ''), 8, '0') = b.custid;

CUSTID   NAME   SURNAME
-------- ------ ----------
00034054 Little Foot
00003356 Scott  Tiger

SQL>

CodePudding user response：

Another way to do it is to use SubStr() function to remove the first two characters (either they are 'CC' or anything else) and then do the Lpad to the length of 8 with '0' characters:

Select b.CUSTID
From tbl_1 a 
Inner Join tbl_2 b on (Lpad(SubStr(a.CUSTID, 3), 8, '0') = b.CUSTID)

Regards...