Checking for new lines and carriage returns

dataservices job design
1

(Re-posting in the most appropriate forum)

I’m writing a function to check that data doesn’t (or does ) have newlines or carriage returns within some fields of the source data, and then replacing those ‘characters’ with a space; the job is writing to flat files, hence the need to remove them. I thought using index would pick this data up, but it doesn’t:

if (index($P_Input_string,’|’ ,1) > 0 or index($P_Input_string,’/n’,1) > 0 or index($P_Input_string,’/r’ ,1) > 0)

begin
$L_Output_string = replace_substr_ext( $P_Input_string ,’|’,’ ‘,null,null);
$L_Output_string = replace_substr_ext( $L_Output_string ,’/n’,’ ‘,null,null);
$L_Output_string = replace_substr_ext( $L_Output_string ,’/r’,’ ',null,null);

end
else
begin
$L_Output_string = $P_Input_string;
end
end

Am I using index incorrectly, or could (should) I use something else?

(Oh, the | is there because that is the delimter on the files)

Thanks

Mart

martinsmith100 (BOB member since 2009-03-19)

2

You can avoid the checking with the INDEX function and just make the replace in the string. Doing it in this way, if there is no especial characters, nothing will happen. I think it can also save some CPU time.

Moreover, instead of three assignment lines, you can use the replace function nested in just one line. We use that in Query mapping in order to clean “strange” characters before a bulk loading into Sybase IQ.

Regards,

Andrés

aidelia :argentina: (BOB member since 2006-02-02)

3

This post was very useful for me. Helped in removing new lines and carriage returns. I used,

replace_substr_ext(

replace_substr_ext( ‘test string’, ‘/n’, ’ ', null, null), ‘/r’, ’ ', null, null)

Thanks,
Janaki

jgopalan :us: (BOB member since 2008-02-25)