Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

One more REGEXP_SUBSTR question.. Need help

Posted on 2009-04-15
22
Medium Priority
?
648 Views
Last Modified: 2013-12-18
Dear experts -
 
                 I am trying to parse the following string to obtain the output
String is
KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789)

Output , I am expecting is (54321)  and (06789)
I have written following sql , but it is giving me (54321)  .. Can you please help?

select
regexp_substr('KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789)',
'\([[:digit:]]+\)') from dual
regards
0
Comment
Question by:akp007
  • 11
  • 10
22 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 24153222
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

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24153787
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);
          

Open in new window

0
 

Author Comment

by:akp007
ID: 24159304
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
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:akp007
ID: 24159317
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
0
 

Author Comment

by:akp007
ID: 24159331
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24159468
you can't have a dynamic number of columns in sql.

what is the highest number you might have?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 24159691
if your strings will always look something like you have above then your first question wasn't really needed you can extract the numeric patterns on the original string without first removing the outer parentheses

once you know your maximum number of fields, you can simply expand the sql statement like in my first example (and lwadwell's first example too)
this following sql will extract the first 20 fields of the form "(9999)"  if you don't have 20 it will extract as many as you have and return null for the rest

SELECT   str,
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 1),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 2),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 3),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 4),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 5),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 6),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 7),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 8),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 9),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 10),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 11),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 12),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 13),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 14),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 15),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 16),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 17),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 18),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 29),
         REGEXP_SUBSTR(str, '\([[:digit:]]+\)', 1, 20)
  FROM   (SELECT   '(KEYWORD . . . . . CodeWord.  xyz8397G3 (54321) AND 7586806(06789))' str
            FROM   DUAL);
0
 

Author Comment

by:akp007
ID: 24162529
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
0
 

Author Comment

by:akp007
ID: 24170649
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24175663
what are the rules for removing xyz8397G3 and 7586806
0
 

Author Comment

by:akp007
ID: 24184116
The rules for  xyz8397G3 and 7586806  are they are alphanumeric.

thanks
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 24184504
try this...

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

Author Comment

by:akp007
ID: 24185888
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24186066
what is the rule for removing 7586806?

i thought you wanted alphanumeric strings removed.

7586806  is only numeric.  
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24186070
sorry, I should have noted that earlier when you first posted that rule
0
 

Author Comment

by:akp007
ID: 24186177
It's my mistake, I said alphanumeric. I would like to retreive the data inside the parenthesis

regards
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24186217
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24186429
or maybe I'm not understanding what you're really asking
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 24186446
see if this does what you're looking for


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

Author Comment

by:akp007
ID: 24188758
This is exactly what I have been looking. Thanks a lot for your help

Best Regards
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24189080
glad I could help!

If nothing else please close the question.

0
 

Author Closing Comment

by:akp007
ID: 31570737
excellent help

Regards
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question