Link to home
Start Free TrialLog in
Avatar of cutie_smily
cutie_smily

asked on

Find CR/LF in the column

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.
Avatar of flow01
flow01
Flag of Netherlands image

try
select * from table where col1 like '%'  || CHR( 13 ) || CHR( 10 )  || '%'.
try this

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

Open in new window

or
where MySearchField like ('%' + cast(char(13) as nvarchar(1)) + cast(char(10) as nvarchar(1)) + '%')

Open in new window

Avatar of cutie_smily
cutie_smily

ASKER

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.
try

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

Open in new window

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.
           
                          where MySearchField like ASCIISTR('%' || CHR(13) || CHR(10) || '%')


It is not working. I think ASCIISTR is not a teradata function.
See attached.
query.txt
What tool are you using against which version of Oracle ?
it works fine with SQLPLUS andORACLE 10g.
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.
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
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent