OraFAQ Forum: SQL & PL/SQL » Regular expression search

Home » SQL & PL/SQL » SQL & PL/SQL » Regular expression search

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

Regular expression search [message #654541]

Fri, 05 August 2016 05:50

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi all

I have a requirement to search for the code when we supplied name for the given string. I had implemented by using Substr/Instr .

Example :

SELECT SUBSTR('ABC-XX,ERT-US,XYZ-IN' , instr('ABC-XX,ERT-US,XYZ-IN' , 'ABC', 1 ,1 ) + 4 , 2) VAL
FROM dual

But this wouldn't helps me. Why because in the production environment length of the code ,and names was not unique.
We have the following information also. But those special characters are always unique.

'ABC-XX,ERT-US,XYZ-IN,QWER-LK,RTGT-PL,MM-NJIU '

if we pass the QWER output should be LK, MM output should be NJIU
Please help to to write the same by using regular expression .

Report message to a moderator

Re: Regular expression search [message #654545 is a reply to message #654541]

Fri, 05 August 2016 06:47

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Please help me to solve the issue

Report message to a moderator

Re: Regular expression search [message #654547 is a reply to message #654545]

Fri, 05 August 2016 07:58

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

SQL> SELECT  REGEXP_SUBSTR(
  2                        'ABC-XX,ERT-US,XYZ-IN,QWER-LK,RTGT-PL,MM-NJIU',
  3                        '(^|,)&SEARCH_STRING-([^,]+)',
  4                        1,
  5                        1,
  6                        NULL,
  7                        2
  8                       ) VAL
  9    FROM  DUAL
 10  /
Enter value for search_string: QWER
old   3:                       '(^|,)&SEARCH_STRING-([^,]+)',
new   3:                       '(^|,)QWER-([^,]+)',

VA
--
LK

SQL> /
Enter value for search_string: MM
old   3:                       '(^|,)&SEARCH_STRING-([^,]+)',
new   3:                       '(^|,)MM-([^,]+)',

VAL
----
NJIU

SQL>

SY.

Report message to a moderator

Previous Topic:	Count group data
Next Topic:	Help using regexp_substr - Urgent Help needed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 22:30:40 CDT 2024