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.
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.
try this
select
*
from
@temptable
where
charindex(CHAR(13), MySearchField, 0)+1 = charindex(CHAR(10), MySearchField, 0)
or
where MySearchField like ('%' + cast(char(13) as nvarchar(1)) + cast(char(10) as nvarchar(1)) + '%')
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.
"Illegal use of Characters,MCHARACTERS,or OCTET_LENGTH functions.
try
where MySearchField like CONCAT(CONCAT('%',TO_CHAR(CHR(13))), CONCAT(TO_CHAR(CHR(13)), '%'))
ASKER
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) || '%')
ASKER
It is not working. I think ASCIISTR is not a teradata function.
See attached.
query.txt
query.txt
What tool are you using against which version of Oracle ?
it works fine with SQLPLUS andORACLE 10g.
it works fine with SQLPLUS andORACLE 10g.
ASKER
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent
select * from table where col1 like '%' || CHR( 13 ) || CHR( 10 ) || '%'.