Home » SQL & PL/SQL » SQL & PL/SQL » How to find records with CR within VARCHAR2
How to find records with CR within VARCHAR2 [message #255773] Wed, 01 August 2007 13:48 Go to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Earlier today I was informed that due to an error in the application feeding a Production V10.2.0.2 DB,
a VARCHAR2 field contained a CARRIAGE_RETURN (CR) character [chr(10)] which caused problems in subsequent processing.

One problem I wanted to solve was to come up with a SQL statement from SQL*Plus that would identify rows which contained 1 or more CR within a specific VARCHAR2 field.

It took me a couple of tries before I found 1 that worked.

CREATE TABLE TESTA (V1 VARCAHR2(9), V2 VARCHAR2(9));
INSERT INTO  TESTA VALUES('123','456');
INSERT INTO  TESTA VALUES('ABC','DEF');
INSERT INTO  TESTA VALUES('abd'||chr(10)||'def',NULL);


How many differnt ways to identify the record with offending CR in it?

[Updated on: Wed, 01 August 2007 13:58] by Moderator

Report message to a moderator

Re: How to find records with CR within VARCHAR2 [message #255785 is a reply to message #255773] Wed, 01 August 2007 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
instr(field,chr(10)) != 0

Regards
Michel
Re: How to find records with CR within VARCHAR2 [message #255788 is a reply to message #255773] Wed, 01 August 2007 14:35 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member

  1  select g.v1,length(b.v1)
  2  from testa g, testa b
  3  where g.rowid = b.rowid
  4*  and  g.v1 <> replace(b.v1,chr(10),'#')
SQL> /

V1        LENGTH(B.V1)
--------- ------------
abd                  7
def


Michel,
I like yours better than mine.

[Updated on: Wed, 01 August 2007 14:36] by Moderator

Report message to a moderator

Re: How to find records with CR within VARCHAR2 [message #255943 is a reply to message #255773] Thu, 02 August 2007 08:43 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
I don't get it. Why not use good old LIKE or REGEXP_LIKE?

-- method 1

select *
from   testa
where  v1 like '%' || chr(10) || '%' ;

V1        V2
--------- ---------
abd
def


-- method 2
select *
from   testa
where  v1 like '%
%' ;

V1        V2
--------- ---------
abd
def


-- method 3
select *
from   testa
where  regexp_like( v1, chr(10) ) ;

V1        V2
--------- ---------
abd
def



--
Joe Fuda
SQL Snippets
Previous Topic: query modification require
Next Topic: How to get count of records in a delete statement
Goto Forum:
  


Current Time: Sat Dec 10 12:46:25 CST 2016

Total time taken to generate the page: 0.21543 seconds