Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Find CR/LF in the column

Posted on 2010-01-07
14
Medium Priority
?
1,588 Views
Last Modified: 2013-12-07
Hi,
I want to search for a carriage return in one of the columns.
select * from table where col1 like '%CR/LF%'.

Col1 is of type VARCHAR(500)

Appreciate your responses. I have to do this in teradata.
0
Comment
Question by:cutie_smily
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 21

Expert Comment

by:flow01
ID: 26205614
try
select * from table where col1 like '%'  || CHR( 13 ) || CHR( 10 )  || '%'.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26205653
try this

select 
	*	
from 
	@temptable 
where
	charindex(CHAR(13), MySearchField, 0)+1 = charindex(CHAR(10), MySearchField, 0)

Open in new window

0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26205681
or
where MySearchField like ('%' + cast(char(13) as nvarchar(1)) + cast(char(10) as nvarchar(1)) + '%')

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cutie_smily
ID: 26205810
Thanks for the quick response. I tried all the solutions and I got the same error as below for all.

"Illegal use of Characters,MCHARACTERS,or OCTET_LENGTH functions.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26206130
try

where MySearchField like CONCAT(CONCAT('%',TO_CHAR(CHR(13))), CONCAT(TO_CHAR(CHR(13)), '%'))

Open in new window

0
 

Author Comment

by:cutie_smily
ID: 26206264
Thanks zadevelope. The problem is not with the concatenation operator. I changed the + to || for concatenation but I am getting the same error as earlier.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26206280
           
                          where MySearchField like ASCIISTR('%' || CHR(13) || CHR(10) || '%')


0
 

Author Comment

by:cutie_smily
ID: 26206683
It is not working. I think ASCIISTR is not a teradata function.
0
 
LVL 32

Expert Comment

by:awking00
ID: 26212099
See attached.
query.txt
0
 
LVL 21

Expert Comment

by:flow01
ID: 26212579
What tool are you using against which version of Oracle ?
it works fine with SQLPLUS andORACLE 10g.
0
 

Author Comment

by:cutie_smily
ID: 26214751
awking00: I think you are right that we have to use hex equivalent of ascii. But somehow the solution you gave throws an error i.e "Invalid Hexadecimal Constant"

flow01: This is in teradata.
0
 
LVL 21

Expert Comment

by:flow01
ID: 26272339
I don't know teradata,

A suggestion try moving the solution to the database

can you create a function
create  or replace
function CRLF return varchar2
begin
  return CHR(13) || CHR(10);
end;
/

select * from table where col1 like '%'  ||  CRLF || '%'

or

create  or replace
function LIKE_CRLF return varchar2
begin
  return '%'  || CHR( 13 ) || CHR( 10 )  || '%';
end;
/

select * from table where col1 like LIKE_CRLF
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1000 total points
ID: 26283250
I think it's because I mistakenly used a capital 'o' instead of zero. Try
where position('0A'XC in col1) > 0
      or position('0D'XC in col1) > 0
0
 

Author Closing Comment

by:cutie_smily
ID: 31674322
Excellent
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question