Home » SQL & PL/SQL » SQL & PL/SQL » Get exact matching string (Oracle 11.2.0.2.0, Windows 2K)
Get exact matching string [message #572392] Tue, 11 December 2012 08:48 Go to next message
yuko
Messages: 65
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
Re: Get exact matching string [message #572393 is a reply to message #572392] Tue, 11 December 2012 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 57606
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
Re: Get exact matching string [message #572395 is a reply to message #572392] Tue, 11 December 2012 09:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1791
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.
Re: Get exact matching string [message #572398 is a reply to message #572395] Tue, 11 December 2012 09:34 Go to previous messageGo to next message
_jum
Messages: 485
Registered: February 2008
Senior Member
Or just:
SELECT COUNT(*) FROM test1 WHERE pattern_series LIKE '%micky_';

  COUNT(*)
----------
         4
1 row selected.
Re: Get exact matching string [message #572399 is a reply to message #572398] Tue, 11 December 2012 09:45 Go to previous messageGo to next message
yuko
Messages: 65
Registered: August 2011
Member
Thank you All.
Re: Get exact matching string [message #572400 is a reply to message #572398] Tue, 11 December 2012 10:14 Go to previous message
Solomon Yakobson
Messages: 1791
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.
Previous Topic: global temporary table not seen by autonomous transaction
Next Topic: Update table with primary key in where clause
Goto Forum:
  


Current Time: Thu Apr 17 11:55:01 CDT 2014

Total time taken to generate the page: 0.08866 seconds