Create a flag when text string exists

[brf10043]

First of all, let me say that I know this is a really bad practice but...

I have an need to search though a column (OUTAGE_SUMMARY) for a free text string (COMMAND CENTRE CRITSIT)  When it exists, I want to create a new flag column called CRITSIT? and populate it with the text "CRITSIT" and when the text doesn't exist, populate the new flag column with "Non Critical".

The challenging part is that after reviewing the data, I can see that the location of the text is not always in the same location in the text block.  Sometimes it is at the start and sometimes it is in the middle of the text block.

I am working with the data source to get a flag added into the source tool, but until then I need to create a short term solution.

Ideas?  Suggestions?

[vishsaggi · Accepted solution]

May be this?

IF(Not Wildmatch(Outage_Summary, '*CRITSIT*')>0, 'Non-Critical', 'CRITSIT')  AS CritsitFlag

[sudeepkm]

you may also try something like below.

if(SubStringCount([OUTAGE_SUMMARY],'CRITSIT')>0,'CRITSIT','Non Critical') as CRITSIT_FLAG