Home » SQL & PL/SQL » SQL & PL/SQL » how to search for two characters in any sequence, with or without alphanumeric characters in between (Oracle 11g, 12c)
how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657975] Tue, 29 November 2016 13:38 Go to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
I would like to search if both C and 2 are in a column. The search has to be case sensitive.

There may/may not be any alphanumeric data between them.


select 'data_available' from dual
where REGEXP_LIKE('C275xAa', 'C2|2C|C?2|2?C');


select 'data_available' from dual
where REGEXP_LIKE('C275xAa', 'C2|2C|C?2|2?C', 'c');


Above code is not case sensitive.

Can you help me with

1) should be case sensitive.
2) may/may not be any alphanumeric/special characters between them.
3) can be any sequence 2C, C2 etc or 2ggfghC or Cjjhjj$55@2

Please advise
Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657976 is a reply to message #657975] Tue, 29 November 2016 13:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
So it is C followed by zero or more characters followed by 2 or 2 followed by zero or more characters followed by C, right? Then pattern is 'C.*2|2.*C'.

SY.
Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657977 is a reply to message #657976] Tue, 29 November 2016 13:54 Go to previous messageGo to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
Thank You YS for your response.


select 'data_available' from dual
where REGEXP_LIKE('275x@22CAa', 'C.*2|2.*C');



What made this case sensitive?

How can I make this case insensitive?

Please advise.
Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657978 is a reply to message #657975] Tue, 29 November 2016 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> with
  2    data as (
  3      select 'C275xAa' val from dual union all
  4      select '2C' val from dual union all
  5      select 'C2' val from dual union all
  6      select 'bla2ggfghCbla' val from dual union all
  7      select 'blaCjjhjj$55@2bla' val from dual union all
  8      select 'Michel' val from dual union all
  9      select 'Cadot' val from dual
 10    )
 11  select val,
 12         case
 13           when REGEXP_LIKE(val, '(C.*2|2.*C)') then 'OK'
 14           else 'ko'
 15         end res
 16  from data
 17  /
VAL               RE
----------------- --
C275xAa           OK
2C                OK
C2                OK
bla2ggfghCbla     OK
blaCjjhjj$55@2bla OK
Michel            ko
Cadot             ko
Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657979 is a reply to message #657977] Tue, 29 November 2016 13:58 Go to previous messageGo to next message
namitanamburi
Messages: 35
Registered: March 2009
Member

select 'data_available' from dual
where REGEXP_LIKE('275x@22cAAa', 'C.*2|2.*C','i');

The above code will make it case insensitive.

However what does this one do ?


select 'data_available' from dual
where REGEXP_LIKE('2c', 'C2|2C','c');


Ora documentation says that this should be make the code search sensitive, which never worked for me.

Please help me understand instead of going by copy paste.
Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657980 is a reply to message #657977] Tue, 29 November 2016 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
How can I make this case insensitive?
REGEXP_LIKE(val, '(C.*2|2.*C)','i')

[Updated on: Tue, 29 November 2016 13:59]

Report message to a moderator

Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657982 is a reply to message #657979] Tue, 29 November 2016 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Ora documentation says that this should be make the code search sensitive, which never worked for me.
You make confusion between case sensitive and case insensitive.
Case sensitive means the data much be of the same case than the pattern.
Case insensitive means the data can be in lower or upper case and the case of the pattern is not significant.

Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657983 is a reply to message #657979] Tue, 29 November 2016 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Please help me understand instead of going by copy paste.
What does this mean?

Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657985 is a reply to message #657983] Tue, 29 November 2016 14:09 Go to previous messageGo to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
Michael,

Thank Yor your time.



select 'data_available' from dual
where REGEXP_LIKE('2dsdc', 'C*2|2*C', 'c');


I mean 'c' after the search_pattern is supposed to make the code search case sensitive(as per the OTN documentation)

I could not get this to work.

Instead of just copy pasting what is working, I wanted to understand, how this is working.
Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657986 is a reply to message #657985] Tue, 29 November 2016 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I mean 'c' after the search_pattern is supposed to make the code search case sensitive
Yes and it does.

Quote:
I could not get this to work.
Which help in no way to know what you did to "not get it work".

Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657987 is a reply to message #657986] Tue, 29 November 2016 14:19 Go to previous messageGo to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
select 'data_available' from dual
where REGEXP_LIKE('2dsdc', 'C*2|2*C', 'c');

In the above code, I am saying C*2, meaning C followed by anything (*) and then a 2

or 2 followed by anything (*) and then a C is what I am looking for,

'c' means , look for only C not c.

This code does not work

Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657988 is a reply to message #657987] Tue, 29 November 2016 14:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I am saying C*2, meaning C followed by anything (*) and then a 2
No, you say 0 or more C followed by a 2.
Anything is ".", "*" is 0 or more previous character (or pattern), "+" is 1 or more previous character.

You make confusion with shell joker character ? and *.
Regular expressions are completely different.

Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657989 is a reply to message #657988] Tue, 29 November 2016 14:31 Go to previous messageGo to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
Michael,

Thanks much for explaining this.
Re: how to search for two characters in any sequence, with or without alphanumeric characters in between [message #657990 is a reply to message #657989] Tue, 29 November 2016 15:36 Go to previous message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
you can do either of the following two tests. They will both work.

when REGEXP_LIKE(upper(val), '(C.*2|2.*C)')

when REGEXP_LIKE(val, '(C.*2|2.*C)','i')
Previous Topic: Star printing in PL/SQL Program Problem
Next Topic: Function MAX
Goto Forum:
  


Current Time: Sun Oct 21 00:38:26 CDT 2018