Home » SQL & PL/SQL » SQL & PL/SQL » Regular expression search
Regular expression search [message #654541] Fri, 05 August 2016 05:50 Go to next message
saipradyumn
Messages: 400
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 .
Re: Regular expression search [message #654545 is a reply to message #654541] Fri, 05 August 2016 06:47 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member

Please help me to solve the issue
Re: Regular expression search [message #654547 is a reply to message #654545] Fri, 05 August 2016 07:58 Go to previous message
Solomon Yakobson
Messages: 3060
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.
Previous Topic: Count group data
Next Topic: Help using regexp_substr - Urgent Help needed
Goto Forum:
  


Current Time: Sat Jun 12 20:58:17 CDT 2021