Search code examples
sqlregexoracle-databasejoinsubstr

join based on condition, remove a part of substring from column value

I would like to join 2 column of 2 tables by replacing a pattern in the start of column.

table1:

ABC_somename_ABC

something_XYZ

Table2:

somename_ABC

somthing_XYZ

Here i have to replace the ABC_ part at the starting not at the end and make an equi join. In second case it should not replace as it doesnot have ABC_

I tried using substr,instr functions to remove till first underscore. SUBSTR (column, INSTR (column, '') + 1) this would start with any underscore but i want that to work only when it starts with ABC or XYZ_

Used regular expression REGEXP_SUBSTR ('ABC_somename_ABC','[^]+', 1,2) is replacing both sides. i have only 2 patterns in the starting which have to removed like ABC, XYZ_ could someone help me on this.

Solution

  • It looks like you just want to join on the end of the string:

    on t1.col like '%' || t2.col

    If you really want to test for "ABC_" at the beginning:

    on (t1.col like 'ABC\_%' and t1.col like '%' || t2.col) or
   (t1.col not like 'ABC\_% and t1.col = t2.col)

    Note: underscore ('_') is a wildcard for the like expression, so it should be escaped.