I have a column (Location Class) If that column starts with PP, I want to set another column (PrePaid) to 1 otherwise 0. I am using this expression: (SUBSTRING([Location Class],1,2) == "'PP") ? "1" : "0" but Prepaid is always 0. I have checked the values in Location Class, there are some starting with PP, but PrePaid is still a 0.

Wrap your [Location Class] with a TRIM ( ) function: SUBSTRING( TRIM ( [Location Class] ) ,1,2) == "'PP") ? "1" : "0" This will eliminate any leading and trailing spaces. If that does not help, put in a derived column of just the SUBSTRING ( [Locations Class] , 1 2 ) and then examine it with a Data Viewer down-stream of the Derived Column. That might help pin-point the issue.Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

I will try it. Thanks

And you could also add a UPPER in case the source is lowercase SUBSTRING(UPPER(TRIM([Location Class])) ,1,2) == "'PP") ? "1" : "0" Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Thanks

check whether the data is in lower case or upper case.that might be the case to get wrong output. if data is in lowercase, below expression will work fine. (SUBSTRING([Location Class],1,2) == "pp") ? "1" : "0" if data is in uppercase, below expression will work fine. (SUBSTRING([Location Class],1,2) == "PP") ? "1" : "0" jeevesh fuloria

It is uppercase. but (SUBSTRING([Location Class],1,2) == "PP") ? "1" : "0" is not working. It is not recognizing the 1st 2 characters as PP so the result is always 0.

Actually you are right. There was an extra ' in before the PP.