0

I am fairly new to SQL and I'm trying to extract some data from an ORACLE DB. My goal is to return rows where the query value lies in the range speicified by the "AA_SYNTAX" column in a table.

For example:

If the "AA_SYNTAX" column is 'p.G12_V14insR' and my search value is 13, I want to return that row. This column is organized as p.%number_%number%. So basically I want to return the two numerical values from this column and see if my query value is between them.

I have all the tables I need joined together and everything, just not sure how to construct a query like this. I know in regex I would do something like "\d+" but im not sure how to translate this into SQL.

Thanks

Rick James
  • 135,179
  • 13
  • 127
  • 222
Brady forcier
  • 1,597
  • 2
  • 11
  • 10
  • 1
    **Numeric range ! Ye be warned !!** because numeric range and regex don't go hand in hand. –  Mar 15 '16 at 18:52
  • I'd probably use regex_Replace as in http://stackoverflow.com/questions/3968178/oracle-replacing-non-numeric-chars-in-a-string twice. Once for before the _ and once for after the _ and then use that as your where clause... `where AA_Syntax between RegEx1Eval and regEx2Eval` – xQbert Mar 15 '16 at 19:01

1 Answers1

0

Using Oracle, you can use Regular Expressions to extract a number from the string.

More specifically, I would look into REGEXP_SUBSTR.

Using the date given in your example above, you could use:

with cte as
(
    select 'p.G12_V14insR' as AA_SYNTAX from dual
)

select 
    REGEXP_SUBSTR(AA_SYNTAX,'p\.[[:alpha:]]+([[:digit:]]+)', 1, 1, NULL, 1) as Bottom 
    ,REGEXP_SUBSTR(AA_SYNTAX,'\_[[:alpha:]]+([[:digit:]]+)', 1, 1, NULL, 1) as Top 
from cte

I'm sure you could clean up the Regular Expression quite a bit, but, given this, you get the value of 14 for Top and 12 for Bottom.

Hope this helps move you in the right direction.

John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • Thanks so much. I think I understand how this is working, but how can I change the hard coded 'p.G12_V14insR' to the value of the column in the table. As in: "DB.TABLE.COLUMN as AA_SYNTAX from dual" – Brady forcier Mar 15 '16 at 19:27
  • Exactly like you'd expect, it's a normal `select` statement. Simply select it out by your rules. – Blindy Mar 15 '16 at 19:31
  • Just don't select from `cte` - I used that to simply create something to select from - Select from your table instead.... `SELECT REGEXP_SUBSTR(AA_SYNTAX, ... FROM MyTable ....` – John Bustos Mar 15 '16 at 19:32