Search code examples
sqloracle-databaseoracle10gora-01722

decoding a text string to use in a join

I'm trying to extract the number from a text string and join it to another table. Here's what I have so far:

SELECT sect.id, 
       sect.section_number, 
       sect.expression, 
       p.abbreviation
  FROM sections sect
  JOIN period p ON SUBSTR(sect.expression, 1, (INSTR(sect.expression,'(')-1)) = p.period_number 
               AND p.schoolid = 73253 
               AND p.year_id = 20
  JOIN courses c ON sect.course_number = c.course_number
 WHERE sect.schoolid = 73253 
   AND sect.termid >= 2000

I read some other threads and figured out how to strip out the number (which always comes before the left parenthesis). The problem is that this only accounts for two of the three styles of data that live in the sect.expression column-

9(A) - check  
10(A) - check

but not

5-6(A)

5-6(A) would kick back an Oracle 01722 invalid number error. Is there a way I could modify the substr... line so that for the 5-6(A) data type it would grab the first number (the 5) and join off of that?

It's worth mentioning that I only have read rights to this table so any solution that depends on creating some kind of helper table/column won't work.

Thanks!

Solution

  • You can use REGEXP_REPLACE

    1) If you want to extract only numbers:

    JOIN period p ON REGEXP_REPLACE(sect.expression, '[^0-9]', '') = p.period_number

    2) If you want to match with the digits in the start of the string and ignore the ones that appear later:

    JOIN period p ON REGEXP_REPLACE(sect.expression, '^(\d+)(.*)', '\1')