Is there any function which can use be instead of substr so that all data is accepted?-CodePudding

This is the code :

Substr(ap.invoice_num,9,6)='B    -'
AND rct.trx_number =
 Substr(rct.trx_number, 0,8)
||'    -' 
|| Substr(rct.trx_number,15,24)

AND rct.trx_number='69362695    -901645'
AND ap.invoice_num='69362695B    -901645'

But these values are not getting accepted:

AND rct.trx_number='15428234 -0001006048'
AND ap.invoice_num='15428234B    -0001006048'

Please suggest what changes to be made in substring function to get these values accepted.

CodePudding user response：

If you have the tables:

CREATE TABLE ap (invoice_num) AS
SELECT '69362695B    -901645' FROM DUAL;

CREATE TABLE rct (trx_number) AS
SELECT '69362695    -901645' FROM DUAL;

Then you need to change 15 to 14 as the substring with spaces you are matching in trx_number is shorter than invoice_num:

SELECT *
FROM   ap CROSS JOIN rct
WHERE  SUBSTR(ap.invoice_num,9,6)='B    -'
AND    rct.trx_number = SUBSTR(rct.trx_number, 0,8) ||'    -'  || SUBSTR(rct.trx_number,14,6)
AND    rct.trx_number='69362695    -901645'
AND    ap.invoice_num='69362695B    -901645'

But it would be simpler just to use SUBSTR again:

SELECT *
FROM   ap CROSS JOIN rct
WHERE  SUBSTR(ap.invoice_num,9,6)='B    -'
AND    SUBSTR(rct.trx_number,9,5)='    -'
AND    rct.trx_number='69362695    -901645'
AND    ap.invoice_num='69362695B    -901645'

But the first two filters you are using won't match if the second two don't so it can be even simpler:

SELECT *
FROM   ap CROSS JOIN rct
WHERE  rct.trx_number='69362695    -901645'
AND    ap.invoice_num='69362695B    -901645'

As the substrings you are matching are present in the larger strings:

However, if you are intending to compare one column to the other (without the 9th character in invoice_num) then you can use:

SELECT *
FROM   ap CROSS JOIN rct
WHERE  SUBSTR(ap.invoice_num,9,6)='B    -'
AND    rct.trx_number = SUBSTR(ap.invoice_num, 0,8) || SUBSTR(ap.invoice_num,10,11)

Note: the CROSS JOIN should probably be an INNER JOIN but I am trying to replicate your WHERE condition.

db<>fiddle here

CodePudding user response：

To me, it looks as if you're about to compare ap.invoice_num and rct.trx_number, but letters and spaces bother you. If that's so, why wouldn't you "remove" them? Something like this (see what JOIN looks like):

SQL> with
  2  ap (invoice_num) as
  3    (SELECT '69362695B    -901645' FROM DUAL),
  4  rct (trx_number) as
  5    (SELECT '69362695    -901645' FROM DUAL)
  6  select *
  7  from ap a join rct r on regexp_replace(a.invoice_num, '[[:alpha:] ]') =
  8                          regexp_replace(r.trx_number , '[[:alpha:] ]');

INVOICE_NUM          TRX_NUMBER
-------------------- -------------------
69362695B    -901645 69362695    -901645

SQL>

Saying that the following values aren't "accepted":

AND rct.trx_number='69396083    -0000881362'
AND ap.invoice_num='15428234B    -00001006048'

Well, why would they be? They've got nothing in common.