0

I am trying to extract a value between the brackets from a string.

For example, I have this string:

No information was found [AI1234].

And I want to get the result between the brackets, i.e. AI1234.

However the expression is not always the same. It may vary.

I am trying to write a query like this:

REGEXP_SUBSTR(mssg, '\((.+)\)', 1, 1, NULL, 1) AS "description" from book

But it is not returning anything.What am I missing?

Also I already tried something like that, the things is that the bracket length may vary. So this one below will return something, but not what I am looking for:

substr(mssg,instr(mssg,'(')-8,10) as "description"
user16523359
  • 1
  • 2

2 Answers2

0

You should do googling more about oracle regexp.

Please try with this.(above Oracle 11g)

SELECT REGEXP_SUBSTR(mssg, '\[[^0-9]*(\d+)[^0-9]*\]', 1, 1, NULL, 1) description
FROM book;

** This helped me to answer here.

UPDATE: This will be OK.

SELECT REGEXP_SUBSTR('No information was found [{AI1234}].', '[[({][^0-9]*(\d+)[^0-9]*[]})]', 1, 1, NULL, 1) description
FROM dual;


UPDATE: Final solution

SELECT REGEXP_SUBSTR('No information was found [{AI1234}].', '[[({]+([^][)(}{]*)[])}]+', 1, 1, NULL, 1) description
FROM dual;

Here, you should take care to [^][)(}{].
DO NOT swap the bracket chracters. I'll quote from Oracle 11g Regexp reference

[ ]

Bracket expression for specifying a matching list that should match any one of the expressions represented in the list. A non-matching list expression begins with a circumflex (^) and specifies a list that matches any character except for the expressions represented in the list.

To specify a right bracket (]) in the bracket expression, place it first in the list (after the initial circumflex (^), if any).

To specify a hyphen in the bracket expression, place it first in the list (after the initial circumflex (^), if any), last in the list, or as an ending range point in a range expression.

This part - [^ ] - was a hard nut to crack and finally I found solution from the reference, that's why I emphasis this.

coding monster
  • 384
  • 4
  • 18
  • what about if I also have letters inside those brackets? It not returning the ones that have letters and numbers inside the bracket. Also, what if I need the 3rd bracket from the message? Thank you – user16523359 Aug 24 '21 at 20:20
  • @user16523359 I tested and updated, this maybe what you wanted. – coding monster Aug 26 '21 at 00:53
  • The updated code is only returning the numbers 1234, but not the letters AI. – user16523359 Aug 30 '21 at 13:41
  • @user16523359 Ahh, you mean all the letters in the brackets, not only digits? yeah, I'll correct it :) – coding monster Aug 31 '21 at 04:20
  • Thank you so much! I have one last question. What if I have more than one bracket and I want to obtain the information not from the first bracket but from the last. I can have up to 3 brackets, but always want to return the information from the last bracket . For example see below: 'No information was found [AI1234].ValueR =[ABC123]. id [CD23478]' and I want everything that is inside the last bracket, so CD23478 THANK YOU!! – user16523359 Sep 01 '21 at 17:28
  • @user16523359 try this `SELECT REGEXP_SUBSTR('No information was found [AI1234].ValueR =[ABC123]. id [CD23478]', '[[({]+([^][)(}{]*)[])}]+[^][)(}{]*$', 1, 1, NULL, 1) description FROM dual;` – coding monster Sep 02 '21 at 03:14
0

If you're looking for a group of digits between square brackets, try this:

WITH
indata(msg) AS (
  SELECT 'No information was found [1234]'
)
SELECT
  REGEXP_SUBSTR(
     msg                    -- the string
   , '^[^[]+[[](\d+)[]].*$' -- the pattern (with a captured
                            -- string "\d+" in round parentheses)
   , 1                      -- start from position 1
   , 1                      -- first found occurrence
   , ''                     -- no modifiers
   , 1                      -- first captured group
   ) AS extr
FROM indata;
 extr 
------
 1234
marcothesane
  • 6,192
  • 1
  • 11
  • 21