Replace Function Does Not Return Empty String


I am using the 2008 Expression Tester. My expression is REPLACE( @[User::StringVar],"JAN", "01" ). The content of the StringVar is 10OCT2010. The tool evaluates the expression as 10OCT2010. The expected result is empty string. As per the function design (http://msdn.microsoft.com/en-us/library/ms141196.aspx) and function synopses in the tool it should return the empty string.
Closed Oct 18, 2010 at 6:34 PM by DarrenSQLIS


DarrenSQLIS wrote Oct 18, 2010 at 1:13 PM

The way in which the REPLACE function behaves is not something I can change, the tool uses Microsoft's evaluator class.

Saying that, I don’t see the problem. Your string value of 10OCT2010 doesn’t match the replace string at all, so there will be no change. I cannot understand why you think the result should be anything other than what it is, perhaps you could explain in more detail what you think is wrong?

arthurz wrote Oct 18, 2010 at 3:44 PM

Let's rephrase the aforementioned question to the following: in what case (please demonstrate) the REPLACE function would return the empty string as per design?

DarrenSQLIS wrote Oct 18, 2010 at 6:32 PM

To convert the string 10OCT2010 into an empty string then you need to replace the entire value with an empty string.

REPLACE( @[User::StringVar],"10OCT2010", "" )

If you want help with the expression syntax a better place to post would be the MSDN forum on SSIS - http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/threads, or actually the help you referenced above includes several examples.

wrote Oct 18, 2010 at 6:34 PM

wrote Feb 14, 2013 at 1:05 AM

wrote May 16, 2013 at 6:42 AM