If condition in ssis

How to achieve the below condition in ssis

if person_id  is like '123%' or '124%'

then details='xyz'

else details

April 30th, 2015 4:03pm

FINDSTRING(  [Col A], "123",1 ) >0?"xyz":(FINDSTRING( [Col A], "124",1 ) >0)?"yxz":"whatever you wnat"

Basically, it is saying find "123" if it finds replace it with "xyz" else find "124" if found replace it with "yxz", if not found replace it with "what ever you want" in your case. Hope it helps!

https://msdn.microsoft.com/en-us/library/ms141680.aspx


  • Edited by Spartaa 10 hours 42 minutes ago
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 4:15pm

You can use the new TOKENCOUNT function

something like

TOKENCOUNT("123",colname) > 1 ? <do for true> : <do for false>

essentially tokencount would return more than 1 if it finds the string

April 30th, 2015 4:29pm

(FINDSTRING([person_id],"123") > 0 ? "xyz" : (FINDSTRING([person_id],"124") ? "xyz" : "yxz"))
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 5:45pm

I'd go for LEFT(person_id,3) = "123" || LEFT(person_id,3) = "124" ? xyz : yxz
April 30th, 2015 7:05pm

FINDSTRING(  [Col A], "123",1 ) >0?"xyz":(FINDSTRING( [Col A], "124",1 ) >0)?"yxz":"whatever you wnat"

Basically, it is saying find "123" if it finds replace it with "xyz" else find "124" if found replace it with "yxz", if not found replace it with "what ever you want" in your case. Hope it helps!

https://msdn.microsoft.com/en-us/library/ms141680.aspx


  • Edited by Spartaa Thursday, April 30, 2015 8:23 PM
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 8:12pm

FINDSTRING(  [Col A], "123",1 ) >0?"xyz":(FINDSTRING( [Col A], "124",1 ) >0)?"yxz":"whatever you wnat"

Basically, it is saying find "123" if it finds replace it with "xyz" else find "124" if found replace it with "yxz", if not found replace it with "what ever you want" in your case. Hope it helps!

https://msdn.microsoft.com/en-us/library/ms141680.aspx


  • Edited by Spartaa Thursday, April 30, 2015 8:23 PM
April 30th, 2015 8:12pm

I'd go for LEFT(person_id,3) = "123" || LEFT(person_id,3) = "124" ? xyz : yxz
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 11:01pm

I'd go for LEFT(person_id,3) = "123" || LEFT(person_id,3) = "124" ? xyz : yxz
April 30th, 2015 11:01pm

Since you want to find the first three characters, you can simply use the LEFT function provided you are on 2012/2014 version as the earlier versions are missing the LEFT function.

Check the link MSDN

Else you can use Findstring or Substring function.

SUBSTRING(person_id,1,3) == "123" || SUBSTRING(person_id,1,3) == "124" ? "xyz" : "yxz"

Hope this will help

Regards



Free Windows Admin Tool Kit Click here and download it now
May 1st, 2015 1:41pm

Since you want to find the first three characters, you can simply use the LEFT function provided you are on 2012/2014 version as the earlier versions are missing the LEFT function.

Check the link MSDN

Else you can use Findstring or Substring function.

SUBSTRING(person_id,1,3) == "123" || SUBSTRING(person_id,1,3) == "124" ? "xyz" : "yxz"

Hope this will help

Regards



  • Edited by JPangging Friday, May 01, 2015 6:00 PM
May 1st, 2015 5:39pm

The LEFT([col],3) == "123" or SUBSTRING([col],1,3) == "123" is the best... alternatives are the findstring or the tokencount but they check for %123% instead of 123%
Free Windows Admin Tool Kit Click here and download it now
May 2nd, 2015 1:48pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics