OraFAQ Forum: SQL & PL/SQL » Get exact matching string

Home » SQL & PL/SQL » SQL & PL/SQL » Get exact matching string (Oracle 11.2.0.2.0, Windows 2K)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Get exact matching string [message #572392]

Tue, 11 December 2012 08:48

yuko
Messages: 58
Registered: August 2011

Member

Hi All,
Need your help for the below requirement.
When I pass the input as 'micky', then i should get the count of records as 4.
I tried to use REGEXP_LIKE, but could not get the result.

Test Case:

DROP TABLE test1;
CREATE TABLE test1( pattern_series VARCHAR2(30));
INSERT INTO test1 VALUES ('qa_micky1');
INSERT INTO test1 VALUES ('qa_micky2');
INSERT INTO test1 VALUES ('pa_micky3');
INSERT INTO test1 VALUES ('la_micky4');
INSERT INTO test1 VALUES ('qa_mickydonald1');
INSERT INTO test1 VALUES ('qa_mickydonald2');
INSERT INTO test1 VALUES ('pa_mickydonald3');
INSERT INTO test1 VALUES ('na_mickydonald4');

SELECT COUNT(*) FROM test1 WHERE pattern_series LIKE '%micky%';
  COUNT(*)
----------
         8
-- I should get the output as 4 not 8

Thank you

Report message to a moderator

Re: Get exact matching string [message #572393 is a reply to message #572392]

Tue, 11 December 2012 08:53

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It depends on the possible data you can have.
With what you posted:

SQL> SELECT COUNT(*) FROM test1 WHERE regexp_like(pattern_series, '_micky\d+');
  COUNT(*)
----------
         4

Regards
Michel

Report message to a moderator

Re: Get exact matching string [message #572395 is a reply to message #572392]

Tue, 11 December 2012 09:08

Solomon Yakobson
Messages: 1398
Registered: January 2010

Senior Member

And without regular expressions:

SQL> select  count(*)
  2    from  test1
  3    where instr(translate(pattern_series,'123456789','000000000'),'_micky0') > 0
  4  /

  COUNT(*)
----------
         4

SQL>

SY.

Report message to a moderator

Re: Get exact matching string [message #572398 is a reply to message #572395]

Tue, 11 December 2012 09:34

_jum
Messages: 450
Registered: February 2008

Senior Member

Or just:

SELECT COUNT(*) FROM test1 WHERE pattern_series LIKE '%micky_';

  COUNT(*)
----------
         4
1 row selected.

Report message to a moderator

Re: Get exact matching string [message #572399 is a reply to message #572398]

Tue, 11 December 2012 09:45

yuko
Messages: 58
Registered: August 2011

Member

Thank you All.

Report message to a moderator

Re: Get exact matching string [message #572400 is a reply to message #572398]

Tue, 11 December 2012 10:14

Solomon Yakobson
Messages: 1398
Registered: January 2010

Senior Member

_jum wrote on Tue, 11 December 2012 10:34

Or just:

Depends:

SQL> insert
  2    into test1
  3    values('qa_mickyy')
  4  /

1 row created.

SQL> insert
  2    into test1
  3    values('qa_donaldmicky5')
  4  /

1 row created.

SQL> SELECT COUNT(*) FROM test1 WHERE pattern_series LIKE '%micky_';

  COUNT(*)
----------
         6

SQL> select  count(*)
  2    from  test1
  3    where instr(translate(pattern_series,'123456789','000000000'),'_micky0') > 0
  4  /

  COUNT(*)
----------
         4

SQL>

SY.

Report message to a moderator

Previous Topic:	global temporary table not seen by autonomous transaction
Next Topic:	Update table with primary key in where clause

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 17:24:38 CDT 2013

Total time taken to generate the page: 1.11045 seconds