A simple method when these are plaguing your script. It hit me again to day in the form of a script that wasn’t doing what I expected, it wasn’t until I looked at the output in a multi-line view did I see it was throwing in a return character in the middle of a command.
You can fix it using a quick SQL trim. BE AWARE THAT VARIABLES ARE SECURE OR YOU MAY PUT YOURSELF AT RISK OF SQL INJECTION HERE.
You first need a variable that is holding just a blank line. I am calling this @CRLF@. Then use the “Script String Functions – Replace” script function. Example with single and multi-line values, your script should look something like this:
SET: @CRLF@ = SQLRESULT[SELECT '\r\n' AS CRLF;] SET: %stringresult% = REPLACE(@TEXTWITHTRAILINGCRLF@,@CRLF@,) SET: %stringresult% = REPLACE(@MULTILINETEXT@,@CRLF@,)
First I used the “Variable Set” function. This is only needed once at the start of the script. I defined:
Set Type: SQL Query Parameter: SELECT '\r\n' AS CRLF; Variable Name: CRLF
Then each time you need to remove the line feeds, call the “Script String Functions” function. I defined:
Operation: Replace Original String: @YOURVARIABLEORVALUEHERE@ Length: <BLANK> Start Position: @CRLF@ Variable: <BLANK for %stringresult%, Or YOUROUTPUTVARIABLENAMEHERE>
Thank you for Darren White for his Regex function and workings above! You can read his original post here http://www.labtechgeek.com/forum/viewtopic.php?f=3&t=3495&p=21491#p21491