Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_LIKE for ? (Oracle RDBMS 10.2, RHEL4)
REGEXP_LIKE for ? [message #433658] Thu, 03 December 2009 09:23 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
We have data that erroneously replaces an apostrophe with a ?.
i.e.
How?s your day?
Isn?t that special?

We do have some data that is supposed to have a ? and I would like to exclude these rows from the search.

How can I use a regular expression search on a column to find all instances of ? that have a character immediately following the question mark?

The below query returns all rows with ?:
select * from tableA where regexp_like(copy, '[url=http://www.orafaq.com/wiki/:punct:]:punct:[/url]');
Re: REGEXP_LIKE for ? [message #433661 is a reply to message #433658] Thu, 03 December 2009 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
compare INSTR with LENGTH
Re: REGEXP_LIKE for ? [message #433663 is a reply to message #433658] Thu, 03 December 2009 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ALWAYS Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: REGEXP_LIKE for ? [message #433665 is a reply to message #433663] Thu, 03 December 2009 10:00 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
create table tablea(
id	number,
copy	varchar2(1000));

insert into tablea
values
(1, 'Aren?t you Bill?');

insert into tablea
values
(2, 'Isn?t that special?');

insert into tablea
values
(3, 'Is this it?');


I would like my search to find just the rows that have a question mark followed by a character.

[Updated on: Thu, 03 December 2009 10:02]

Report message to a moderator

Re: REGEXP_LIKE for ? [message #433667 is a reply to message #433665] Thu, 03 December 2009 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
08:08:29 SQL> @qmark
08:08:33 SQL> create table tablea(
08:08:33   2  id	number,
08:08:33   3  copy	varchar2(1000));

Table created.

08:08:33 SQL> 
08:08:33 SQL> insert into tablea
08:08:33   2  values
08:08:33   3  (1, 'Aren?t you Bill?');

1 row created.

08:08:33 SQL> 
08:08:33 SQL> insert into tablea
08:08:33   2  values
08:08:33   3  (2, 'Isn?t that special?');

1 row created.

08:08:33 SQL> 
08:08:33 SQL> insert into tablea
08:08:33   2  values
08:08:33   3  (3, 'Is this it?');

1 row created.

08:08:33 SQL> select * from tablea where instr(copy,'?') <> length(copy);

	ID
----------
COPY
--------------------------------------------------------------------------------
	 1
Aren?t you Bill?

	 2
Isn?t that special?


08:08:33 SQL> rollback;

Rollback complete.

08:08:33 SQL> drop table tablea;

Table dropped.

08:08:33 SQL> 
Re: REGEXP_LIKE for ? [message #433695 is a reply to message #433665] Thu, 03 December 2009 12:39 Go to previous messageGo to next message
joy_division
Messages: 4615
Registered: February 2005
Location: East Coast USA
Senior Member
TLegend33 wrote on Thu, 03 December 2009 11:00

I would like my search to find just the rows that have a question mark followed by a character.


Are you sure you want entire rows and not just words with embedded question marks?
Re: REGEXP_LIKE for ? [message #433720 is a reply to message #433695] Thu, 03 December 2009 16:17 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
joy_division wrote on Thu, 03 December 2009 13:39

Are you sure you want entire rows and not just words with embedded question marks?

joy_division asks the important question.

SQL> select * from tablea;

        ID COPY
---------- ----------------------------------------
         1 Aren?t you Bill?
         2 Isn?t that special?
         3 Is this it?
         4 How about two in one line?  Hmmm?
         5 Let?s do three?  OK?

SQL> select id, copy
  2  from tablea
  3  where regexp_like(copy, '\?\S');

        ID COPY
---------- ----------------------------------------
         1 Aren?t you Bill?
         2 Isn?t that special?
         5 Let?s do three?  OK?

Otherwise, BlackSwan's solution works fine.
Previous Topic: generic function for contains
Next Topic: Escape Single Codes in Dynamic Script
Goto Forum:
  


Current Time: Sun Sep 25 14:40:38 CDT 2016

Total time taken to generate the page: 0.07908 seconds