Oracle Nested SUBSTR Compare

Question

I'm trying to query a table, and my criteria requires me to match a literal with a substring of a column, like this:

SELECT .........
FROM issuer_table
WHERE owner = "ABC"
  AND {substr logic} = v_cik_nbr;

My column values are like this: "http://www.url.com/data/Archives/931015/000120767912044794/doc4.xml"

So, I've been playing with statements like this:

SELECT SUBSTR(x,1,INSTR(x,'/')-1)
FROM (SELECT SUBSTR('http://www.url.com/data/Archives/931015/000120767912044794/doc4.xml',
              INSTR('http://www.url.com/data/Archives/931015/000120767912044794/doc4.xml','/',1,7) +1) AS x
      FROM dual);

So, that actually gets me the value I need to compare against: "000120767912044794". But, how to incorporate that into the WHERE clause?

Please provide sample data and desired results, as tabular text. — GMB, Oct 23 '20 at 23:32
Your test statement returns `null`: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=d8c18a2b9067070ef204c06d67764293 — GMB, Oct 23 '20 at 23:37

score 0 · Answer 1 · answered Oct 23 '20 at 23:39

Your substr isn't quite right if it's supposed to return 000120767912044794 in your example. If you want to return from between the second last and the last '/' then your inner instr needs to be instr(expression,'search',-1,2) - start from the end and return the second match.

SELECT SUBSTR(x,1,INSTR(x,'/')-1)
FROM (
SELECT SUBSTR('http://www.url.com/data/Archives/931015/000120767912044794/doc4.xml',
              INSTR('http://www.url.com/data/Archives/931015/000120767912044794/doc4.xml','/',-1,2) +1) AS x
FROM dual
)

To use it as an expression in your query you would copy the expression into your main substr so that it can be used at the same level:

SUBSTR(SUBSTR(my_url,INSTR(my_url,'/',-1,2) +1),1,INSTR(SUBSTR(my_url,INSTR(my_url,'/',-1,2) +1),'/')-1)

Which then makes your query

SELECT .........
FROM issuer_table
WHERE owner = "ABC"
  AND SUBSTR(SUBSTR(my_url,INSTR(my_url,'/',-1,2) +1),1,INSTR(SUBSTR(my_url,INSTR(my_url,'/',-1,2) +1),'/')-1) = v_cik_nbr;

Alternatively, you could take the hit and use regexp:

SELECT .........
FROM issuer_table
WHERE owner = "ABC"
  AND regexp_substr(my_url,'.*\/([^\/]*)/[^\/]*',1,1,'i',1) = v_cik_nbr;

The substr/instr expression can be indexed, the regexp one can't be (as it can have non-deterministic behaviours)

score 0 · Answer 2 · 2020-10-24T01:10:40.367

Wrong approach.

Suppose you must compare a long string like 'abcdef/0123456789/blahblahblah/etc' to the string '0123456789'. One way is to play silly tricks with SUBSTR and INSTR, or regular expressions, or whatnot. Another, simpler way is to, instead, use the FULL string and compare it against the string of digits, using the LIKE operator with wildcards.

It would look like this:

... where <string> LIKE '%/0123456789/%'

Here % is a wildcard (standing for any string at all), and / has no special meaning. Including / on both sides of the digit-string we ensure that the required fragment doesn't appear as a substring of the desired v_cik_number, but instead it is the full such number.

In your case, something like:

where <string> like '%/' || v_cik_number || '/%'

assuming that v_cik_number is in fact a string and not a number; if it is a number, you must handle leading zeros, which is not difficult, you just must know ahead of time what the total length of a "v_cik_number" string is supposed to be. And assuming, of course, that the v_cik_number must appear enclosed by forward slashes, and it cannot be confused with some other substring of the URL.

Oracle Nested SUBSTR Compare

2 Answers2