Linefeed and carriage return in Char field 1

[kjnorman]

I am more an Oracle user than Sybase, so please excuse my short comings with regards to Sybase.

I am extracting data from a sybase (System 11) table that contains a field with text information. The text can contain linefeeds and carriage returns (Char(13) + Char(10)), and so when I run an ISQL script these linefeeds corrupt the output.

I would like to replace these with a different character during my export, but I can not get the charindex function to select on this.

For example:
set nocount on
declare @t varchar(255), @lf varchar(2), @msg varchar(255)
select @lf = char(13) + char(10)
select @t = "Hello " + @lf + "World"
print @t
select @msg = 'Linefeed at ' + convert(char(2),charindex(@t,@lf))
print @msg

produces:
Hello
World
Linefeed at 0

Any idea how I do this?
I would like to do the Oracle equivalent of replace(text,chr(13)||chr(10),' ')

Thanks
Kerry

 

[sybaseguru]

Kerry,

No problem. That is what you have in Oracle

DECLARE abc varchar2(255);
result varchar2(255) default NULL;
BEGIN
  abc := 'hello '||chr(13)||chr(10)||'world';
  dbms_output.put_line('abc = '||abc);
  SELECT REPLACE (abc,chr(13)||chr(10),'') INTO result FROM dual;
  dbms_output.put_line('Result = '||result);
END;
 /
abc = hello
world
Result = hello world
PL/SQL procedure successfully completed.

Let us try this in Sybase

1> set nocount onJ6.1059684630" 16L, 525C written
2> declare @x char(255), @y int, @search varchar(2), @replace varchar(2), @strl int
3> select @x = 'hello'+char(10)+' world', @search=char(10), @replace=' '
4> select 'Initial String: '+@x
5> --select 'Search  String: '+@search
6> --select 'Replace String: '+@replace
7> select @strl=char_length(@search)
8> --select 'Length='+convert(varchar,@strl)
9>
10> select @y=charindex(@search,@x)
11> while @y >0
12> begin
13> select @x=substring(@x,1,@y-1)+@replace+substring(@x,@y+@strl,255)
14> select @y=charindex(@search,@x)
15> end
16> select 'Final String: '+@x
17> go
                                                                                                                                                            
------------------------------------
 Initial String: hello
 world                                                      

----------------------------------
 Final String: hello  world

Hope this helps and good luck

 

[sybaseguru]

Hi Kerry,

In new Sybase 12.5.0.3 version you have str_replace very similar to Oracle and MSSQL replace

1> select str_replace("chevy, ford, mercedes", "","toyota")
2> go

 ------------------------------------------------------------------------------------------------------------------------------
 chevy,toyotaford,toyotamercedes