Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Subhash.

Asked: September 21, 2017 - 4:54 pm UTC

Last updated: September 25, 2017 - 3:42 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi,

I have a cust_bug_test table with 2 columns(ROOT_CAUSE, BUG_NUMBER) like as follows:

create table cust_bug_test(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER NOT NULL PRIMARY KEY)
insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('This is a #test issue.', 25940149)
insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('This is a #timing issue', 25768241)
insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('#timing', 26167402)
insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('this is #test issue.', 23250474)
insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('This is a #code issue', 23742921)

Now my requirement is to extract the hashtag word(without #) from fist column and corresponding BUG_NUMBER column from second column.

For example my query should return 2 columns like as follows:

TAG BUG_NUMBER
----- --------
test 25940149
timing 25768241
timing 26167402
test 23250474
code 23742921


Please let me know the query for above result.
I am trying to write the query for above result, but I could get only the first column after filtering #, and not able to get the corresponding BUG_NUMBER.

select replace(tag,'#','') as tag from(
with texttab as
(
SELECT
ROOT_CAUSE as text
from cust_bug_test where ROOT_CAUSE is not NULL
)
SELECT regexp_substr(text,'\#[a-z0-9_]+',1,level,'i') as tag
FROM texttab
CONNECT BY regexp_substr(text,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL) t

My above query results only 1 column.:

TAG
----
test
timing
timing
test
code


Thanks,
Subhash

and Connor said...

Something like this ?


SQL> @drop cust_bug_test

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL> create table cust_bug_test(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER NOT NULL PRIMARY KEY);

Table created.

SQL> insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('This is a #test issue.', 25940149);

1 row created.

SQL> insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('This is a #timing issue', 25768241);

1 row created.

SQL> insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('#timing', 26167402);

1 row created.

SQL> insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('this is #test issue.', 23250474);

1 row created.

SQL> insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('This is a #code issue', 23742921);

1 row created.

SQL>
SQL> select regexp_substr(root_cause,'\#[a-z0-9_]+',1,1,'i') hashtag, bug_number
  2  from cust_bug_test;

HASHTAG                        BUG_NUMBER
------------------------------ ----------
#test                            25940149
#timing                          25768241
#timing                          26167402
#test                            23250474
#code                            23742921

5 rows selected.

SQL>
SQL> select regexp_substr(root_cause,'\#([a-z0-9_]+)',1,1,'i',1) hashtag, bug_number
  2  from cust_bug_test;

HASHTAG                        BUG_NUMBER
------------------------------ ----------
test                             25940149
timing                           25768241
timing                           26167402
test                             23250474
code                             23742921

5 rows selected.

SQL>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Subhash Kumar, September 22, 2017 - 4:31 am UTC

Hi Connor,

Thanks for your response, this will help me.

There is one more issue, like in my table if I insert rows like

insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('', 25940150)

insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('test issue', 25940151)

then
select regexp_substr(root_cause,'\#([a-z0-9_]+)',1,1,'i',1) tag, bug_number from cust_bug_test
should not return these row in output. In this case it should return only those 5 rows you mentioned in your response.

It should return a row only if there was a word with prefixed # in first column.

Could you please let me know the query for that?

Subhash Kumar, September 22, 2017 - 12:39 pm UTC

Also one more issue I am trying to fix is as follows:

If I insert a rows like, insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('This is a #test issue and #problem should be looked at QA end', 25940141)

Then if I do a select call it should return output like as follows:

HASHTAG BUG_NUMBER
-------- ----------
test 25940141
problem 25940141

Please let me know the modified query for this
Connor McDonald
September 25, 2017 - 3:42 am UTC

You can see a solution for that later in this thread, but hopefully you're getting an understanding now of how to provide a test case that accurately describes your requirement.

Query

Rajeshwaran Jeyabal, September 22, 2017 - 1:23 pm UTC 

demo@ORA12C> select * from cust_bug_test ;

ROOT_CAUSE                          BUG_NUMBER
----------------------------------- ----------
                                      25940150
test issue                            25940151
This is a #test issue and #problem    25940141
should be looked at QA end

This is a #test issue.                25940149
This is a #timing issue               25768241
#timing                               26167402
this is #test issue.                  23250474
This is a #code issue                 23742921

8 rows selected.

demo@ORA12C> select *
  2  from cust_bug_test t
  3  where regexp_like( root_cause, '#(\S+)');

ROOT_CAUSE                          BUG_NUMBER
----------------------------------- ----------
This is a #test issue and #problem    25940141
should be looked at QA end

This is a #test issue.                25940149
This is a #timing issue               25768241
#timing                               26167402
this is #test issue.                  23250474
This is a #code issue                 23742921

6 rows selected.

demo@ORA12C>
demo@ORA12C> select regexp_substr(root_cause,'#(\S+)',1,x1,'i',1) hash_tag , bug_number
  2  from (
  3  select t.* , regexp_substr( root_cause, '#(\S+)',1,1,'i',1) x
  4  from cust_bug_test t
  5  where regexp_like( root_cause, '#(\S+)')
  6      ) , lateral( select level x1
  7          from dual
  8          connect by level <= regexp_count(root_cause,'#(\S+)'))
  9  order by 2, 1
 10  /

HASH_TAG   BUG_NUMBER
---------- ----------
test         23250474
code         23742921
timing       25768241
problem      25940141
test         25940141
test         25940149
timing       26167402

7 rows selected.

demo@ORA12C>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library