2

still learning about SSIS

I have an excel source in SSIS, I need to create a case statement based on one of the columns [Teacher]

but as a derived column (as it does not work in the SQL statement)

the normal SQL would be

CASE WHEN [Teacher] like '%A%' then [Teacher] else null end as [Teacher]

I have tried to look for examples, but can't find any with use of the like '%A%'

please help

Hadi
  • 36,233
  • 13
  • 65
  • 124
Joeysonic
  • 255
  • 3
  • 11
  • Since you're learning, what were some of the search terms you were using? – billinkc Aug 11 '20 at 15:35
  • 1
    search google, it's actually better than searching stackoverflow for stackoverflow results: ssis derived column using like. First result is exactly what you need: https://stackoverflow.com/questions/4739230/is-it-possible-to-perform-a-like-statement-in-a-ssis-expression – KeithL Aug 11 '20 at 16:45

1 Answers1

1

You should use conditional with FINDSTRING() function as following (assuming that [Teacher] column is of type DT_WSTR):

FINDSTRING([Teacher],"A",1) > 0 ? [Teacher] : NULL(DT_WSTR,50)
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thanks for this , but how do I amend for the opposite to bring back the values in same column , but not wanting the ones that have "A" I have tried FINDSTRING([Teacher],"A",1) < 0 ? [Teacher] : NULL(DT_WSTR,50) and FINDSTRING([Teacher],"A",1) =1 ? [Teacher] : NULL(DT_WSTR,50). with no joy. please help – Joeysonic Aug 13 '20 at 08:10
  • @Joeysonic use `= 0` – Hadi Aug 13 '20 at 17:15
  • thank you its ==0 .. fab. last issue I have its case sensitive I have one record that's ...a...instead of A ...I have tried the following FINDSTRING([Teacher],"A",1) + FINDSTRING([Teacher],"a",1) > 0 ? [Teacher] : NULL(DT_WSTR,50), but record still there, how can I please amend – Joeysonic Aug 14 '20 at 07:41
  • It was unexpected to find one record with a small 'a', but have to filter them out some how , please help x – Joeysonic Aug 14 '20 at 08:15
  • Try using `FINDSTRING(UPPER([Teacher]) ,"A",1)` – Hadi Aug 14 '20 at 18:03