One more REGEXP_SUBSTR question.. Need help

Hi akp007,

The substr will only match to one value at a time.  You can do a second REGEXP_SUBSTR to ask for the second occurance or use a REGEXP_REPLACE:

lwadwell

-- The '1' is the starting position, the '2' requests the second occurance
SELECT REGEXP_SUBSTR('KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789)',
                     '\([[:digit:]]+\)',1,2) FROM dual 
--
SELECT REGEXP_REPLACE('KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789)',
                     '(.*)(\([[:digit:]]+\))(.*)(\([[:digit:]]+\))(.*)',
                     '\2\4') AS both_values
  FROM dual

Select allOpen in new window

do you want the output to be...
 2 columns in one row?
 1 column in 2 rows?  
 or something else?

the first two options can be achieve as below...


I am curious, this question appears to be related to your previous regexp question.  
Are you breaking a task in to pieces?
What are you trying to do overall?

--option 1
 
SELECT   str,
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 1),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 2)
  FROM   (SELECT   'KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789)' str
            FROM   DUAL);
 
 
-- option 2
SELECT   str, n, REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, n)
  FROM   (SELECT   'KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789)' str
            FROM   DUAL),
         (    SELECT   LEVEL n
                FROM   DUAL
          CONNECT BY   LEVEL <= 2);
          

Select allOpen in new window

You are right. The previous question I have posted is related to this question. We have the requirement, if the string is like
(KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789))
then
we need to get the following 2 records out of it.

Descritpion                                                                                                        Attribute_1      Attribute_2
----------------------------------------------------------------------------------------------------------------------------
(KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789))   xyz8397G3     54321
(KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789))   7586806          06789

Thank you

I forgot to mention that in the string specified , there is no restriction that I can have only 2 sets, I can have more

regards

I forgot to mention that in the string specified , there is no restriction that I can have only 2 sets, I can have more

regards

you can't have a dynamic number of columns in sql.

what is the highest number you might have?

Thanks for the help. What I am planning to do is I am doing to introduce a counter and tackle it. I am working on it and I will update it. But your solution and suggestion works

Regards

Hi , I just have a quick question, I would like to get the following string

Input is
 KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789)

Output should be

KEYWORD . . . . . CodeWord.  (54321) AND (06789)

with following sql
select
regexp_replace('KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789)',
'\([[:digit:]]+\)') from dual  
I could able to get
KEYWORD . . . . . CodeWord.  xyz8397G3  AND 7586806

But I am looking for the string KEYWORD . . . . . CodeWord.  (54321) AND (06789)

Thank you

what are the rules for removing xyz8397G3 and 7586806

The rules for  xyz8397G3 and 7586806  are they are alphanumeric.

thanks

I tried this one, I am getting the following

KEYWORD . . . . . CodeWord.   (54321) AND 7586806 (06789)

xyz8397G3z  is set to null , But 7586806  is not setting to null.  Trying to get the following string out of the above

KEYWORD . . . . . CodeWord.  (54321) AND (06789)

I tried to change the above  as follows, But it is setting up the data inside paranthesis also to Null. Please help

SELECT REGEXP_REPLACE(
           'KEYWORD . . . . . CodeWord.  xyz8397G3z (54321) AND 7586806 (06789)',
           '([0-9]+[a-zA-Z])|([a-zA-Z]+[0-9]|[0-9])[0-9a-zA-Z]*',
           NULL
       )
  FROM DUAL

Thanks

what is the rule for removing 7586806?

i thought you wanted alphanumeric strings removed.

7586806  is only numeric.  

sorry, I should have noted that earlier when you first posted that rule

It's my mistake, I said alphanumeric. I would like to retreive the data inside the parenthesis

regards

I've already posted how to retrieve the data inside the parentheses.

Your new question is how to extract everything "except" a few special substrings.
but I don't know how you want to identify your special substrings.
this new question really should be a "new" question anyway since it's doesn't appear to be related to the original, except for using the same sample string.  It's a new request entirely.

or maybe I'm not understanding what you're really asking

This is exactly what I have been looking. Thanks a lot for your help

Best Regards

glad I could help!

If nothing else please close the question.

excellent help

Regards