find carriage return in a varchar2

[zapster]

Hi,

How can i find out if there are carriage returns in a varchar2, with sql?

thanks

 

[OraCool]

Zapster

Use This:
select ThatColumn
from ThatTable
where instr(ThatColumn,chr(13))>0;

Did it work?

 

[kbear]

When I try it, I get:
Server: Msg 195, Level 15, State 10, Line 3
'chr' is not a recognized function name.

Another way to find it is
select * from tablename
where columnname like '%
%'

The % are on different lines to capture the hard return. I need to replace the hard returns in my varchar column with a space. Any advice on doing that?

 

[rac2]

In MS SQL Server you could use the CHARINDEX(), CHAR() and STUFF() functions. See Books Online.