Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_INSTR (Oracle 12c,NT)
REGEXP_INSTR [message #665156] Wed, 23 August 2017 09:36 Go to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
Thanks for your guidance and support.

I have a requirement to find the string that has an alphabet in 1,2 and 8th position and number in 3 and 7 position.
I used regexp_instr(testval,'[A-Za-z]',2)=2. It gives the required value.
Is there any way to find all this in one statement.
The same requirement is required for opposite way.
Request your suggestion and help.

Thanks.

create table testchar(testval varchar2(8));
insert into testchar values('A2345678');
insert into testchar values('1B345678');
insert into testchar values('1234567H');
insert into testchar values('12345678');
insert into testchar values('!@3$%^()');
insert into testchar values('!@#$%^7*');
Re: REGEXP_INSTR [message #665163 is a reply to message #665156] Wed, 23 August 2017 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select testval,
  2         case when regexp_like(testval,'^[[:alpha:]]{2}\d...\d[[:alpha:]]') then 'OK' else 'ko' end flag
  3  from testchar
  4  /
TESTVAL  FL
-------- --
A2345678 ko
1B345678 ko
1234567H ko
12345678 ko
!@3$%^() ko
!@#$%^7* ko
MI3CHE1C OK

7 rows selected.
Re: REGEXP_INSTR [message #665168 is a reply to message #665163] Thu, 24 August 2017 02:04 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Many thanks Mike.

To get the opposite way that's other than 1,2 and 8 postion if it is number and 3 and 7th position if it is char, shall I use
case when not regexp_like(testval,'^:alpha:{2}\d...\d:alpha:') then 'OK' else 'ko' end flag.
Will it resolve? Your suggestion is highly appreciated.

Thanks.
Re: REGEXP_INSTR [message #665188 is a reply to message #665168] Thu, 24 August 2017 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The opposite of "a AND b AND c" is "NOT a OR NOT b OR NOT c".
What is your actual question, you sentence is not clear?

Re: REGEXP_INSTR [message #665252 is a reply to message #665188] Mon, 28 August 2017 02:10 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi Mike,

My question is . If I don't have char at the 1,2 and 8 positions, also no numeric values at 3 and 7th position, shall I use the NOT operator to get this done?

LIke: '12MICHE8'
Your solution provides me to get the string which is having char at 1,2,8 and numeric at 3 and 7 position which is fine.

Thanks.

Re: REGEXP_INSTR [message #665254 is a reply to message #665252] Mon, 28 August 2017 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
If I don't have char at the 1,2 and 8 positions,
If you don't have at ANY place or at ALL places?
M2MICHE8 good or not?
MI3CHELC good or not?
...

Re: REGEXP_INSTR [message #665321 is a reply to message #665254] Wed, 30 August 2017 00:49 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi Mike,
Both these are good.
M2MICHE8 good
MI3CHELC good
Re: REGEXP_INSTR [message #665322 is a reply to message #665321] Wed, 30 August 2017 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You didn't answer my question:
Quote:
If you don't have at ANY place or at ALL places?
Re: REGEXP_INSTR [message #665323 is a reply to message #665322] Wed, 30 August 2017 02:37 Go to previous message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
don't have at ANY place
Previous Topic: Question about printing the result of a recursive subquery factoring with line indents
Next Topic: Conversion into Clob from Varchar2
Goto Forum:
  


Current Time: Thu Mar 28 05:54:37 CDT 2024