How to join table on field value substring without fixed length?-CodePudding

I have table a and b.in table a there’s a field a1 which has two formats xxxxx or xxxxx.xx. I want to query a and b based on if a1 Contains no dot then a.a1=b.b1 else substring(a.a1,0 to dot pos) =b.b1.How to write the join condition if the second format xxxxx.xx is not fixed length? thx.

CodePudding user response：

You can use:

SELECT *
FROM   a
       INNER JOIN b
       ON (a.a1 = b.b1 OR a.a1 LIKE b.b1 || '.%')

or:

SELECT *
FROM   a
       INNER JOIN b
       ON ((a.a1 NOT LIKE '%.%' AND a.a1 = b.b1) OR a.a1 LIKE b.b1 || '.%')

Which for the sample data:

CREATE TABLE a (a1) AS 
SELECT 'abcde' FROM DUAL UNION ALL
SELECT 'abc.def' FROM DUAL UNION ALL
SELECT 'abcd.ef' FROM DUAL;

CREATE TABLE b (b1) AS 
SELECT 'abc' FROM DUAL UNION ALL
SELECT 'abcd' FROM DUAL UNION ALL
SELECT 'abcde' FROM DUAL;

Outputs:

A1 B1

abcde abcde

abc.def abc

abcd.ef abcd

A1	B1
abcde	abcde
abc.def	abc
abcd.ef	abcd

db<>fiddle here

CodePudding user response：

A join can be done on an expression. So the solution is to create an expression on the table with the format 'xxxx.xx' that extracts the value the join needs to be on.

This can be done using an expression like this:

CASE WHEN INSTR(a.ca1,'.') = 0 
  THEN ca1 
  ELSE SUBSTR(a.ca1,1,INSTR(a.ca1,'.') - 1) 
END

The function will return everything up to the first "." if the sting contains a ".", else it will return the string. This can be achieved with REGEXP_REPLACE as well but that will be less performant than the good old SUBSTR.

WITH tablea (ca1) AS
(
  SELECT 'ABC.XX' FROM DUAL UNION ALL
  SELECT 'CDE.HHAA88' FROM DUAL UNION ALL
  SELECT 'A123.XYZ' FROM DUAL UNION ALL
  SELECT 'KLM' FROM DUAL 
  
),
tableb (cb1) AS
(
  SELECT 'ABC' FROM DUAL UNION ALL
  SELECT 'DEF' FROM DUAL UNION ALL
  SELECT 'KLM' FROM DUAL UNION ALL
  SELECT 'A123' FROM DUAL
)
SELECT a.ca1
  FROM tablea a 
       JOIN tableb b ON b.cb1 = CASE WHEN INSTR(a.ca1,'.') = 0 THEN ca1 ELSE SUBSTR(a.ca1,1,INSTR(a.ca1,'.') - 1) END ;

CA1       
----------
ABC.XX
KLM
A123.XYZ