Home » SQL & PL/SQL » SQL & PL/SQL » Regular Express for searching Name (oracle 10g 10.2.0)
Regular Express for searching Name [message #397076] Wed, 08 April 2009 15:41 Go to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Hi I am trying to find the employee name using regular expression.

The table can contain values like:
Doug, Campbell
doug, campbell
Doug, cambell
doug, Campbell

I need a regular expression to list all these above names when I search for 'Doug, Campbell'

Thanks,
Mahesh
Re: Regular Express for searching Name [message #397078 is a reply to message #397076] Wed, 08 April 2009 16:19 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Why a regular expression ? Sounds like homework ??

Check REGEXP_LIKE or could use Case-Insensitive Search or simply the UPPER/LOWER function.
Re: Regular Express for searching Name [message #397083 is a reply to message #397076] Wed, 08 April 2009 17:26 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Ya, I would like to use Reg Exp because it is very efficient to search for multiple records with the same name..
Re: Regular Express for searching Name [message #397084 is a reply to message #397076] Wed, 08 April 2009 17:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I would like to use Reg Exp
So proceed to do so.
Nobody here can stop you from doing as you think best.

Enjoy!
Re: Regular Express for searching Name [message #397085 is a reply to message #397084] Wed, 08 April 2009 17:32 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Thank you for your suggestion but I could not able to achieve it, I can only able to search for the firstname but the combination..thats what i am struggling with..
Re: Regular Express for searching Name [message #397088 is a reply to message #397085] Wed, 08 April 2009 18:25 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
What have you tried so far with the regular expression query ? Can you post your query ?
Re: Regular Express for searching Name [message #397089 is a reply to message #397076] Wed, 08 April 2009 18:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I can only able to search for the firstname
> it is very efficient to search for multiple records

You are contradicting yourself.
Why do you insist on solution you can not implement?

It is easier to use CaseInsensitive search than regular expression as you have discovered.

Is this a home work problem?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.
Re: Regular Express for searching Name [message #397137 is a reply to message #397083] Thu, 09 April 2009 03:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
INSTR and UPPER will do the job quite adequately, and faster than REGEXP_LIKE

If you insist on using the Regexp, you need to look at the documentation (link provided) and look for 'match parameter'
Re: Regular Express for searching Name [message #397198 is a reply to message #397076] Thu, 09 April 2009 06:53 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hope following code will help you.

SQL>insert into test values ('Abc Xyz');

1 row created.

SQL>insert into test values ('abc Xyz');

1 row created.

SQL>insert into test values ('Abc xyz');

1 row created.

SQL>insert into test values ('abc xyz');

1 row created.

SQL>select count(*) from test;

  COUNT(*)
==========
        42

1 row selected.

SQL>select * from test
  2  where regexp_like(testcol, '(A|a)bc (X|x)yz');

TESTCOL
==================================================
Abc Xyz
abc Xyz
Abc xyz
abc xyz

4 rows selected.


regards,
Delna
Re: Regular Express for searching Name [message #397202 is a reply to message #397198] Thu, 09 April 2009 06:57 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
So how are you supposed to know what letter to accommodate for in regards to case sensitivity?

You should look at 'match parameter' as JRowbottom recommended.
Re: Regular Express for searching Name [message #397206 is a reply to message #397076] Thu, 09 April 2009 07:06 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes, JRowbottom sir and ebrian sir, both are right.
Before replying I didn't read that portion by mistake.
You should specify match parameter 'i' for case insensitive.

regards,
Delna
Previous Topic: CONNECT BY PRIOR
Next Topic: Search particular object used in any of my PL/SQL code (merged 3)
Goto Forum:
  


Current Time: Tue Feb 18 13:48:56 CST 2025