How to check if a specific text is available in a string?

[PeterDD]

Hello I,m trying to find a specific text in a string:
in a Expression:
Using IF(instr([fieldname];'*help*')>0)
THEN ('is present')
ELSE ('not present')

the results shows only 'not present'. but in the string help is present?

What is going wrong?

[Lynn]

Hello I,m trying to find a specific text in a string:
in a Expression:
Using IF(instr([fieldname];'*help*')>0)
THEN ('is present')
ELSE ('not present')

the results shows only 'not present'. but in the string help is present?

What is going wrong?

Are the asterisks meant to be wildcards? Check the syntax of INSTR but I would suspect it is looking for the string help surrounded by asterisks which perhaps does not exist. Try just using 'help' instead.

[PeterDD]

Unfortunatly It the text in the string somethimes is marked with a captitol like Help or as help. or as helpmeplease
so The wildcards are necessary...

[BigChris]

Could you use the cognos "contains" function? You might want to convert everything to upper case before you do the comparison:

Code Select Expand

CASE
when UPPER([fieldname]) contains 'HELP' then 'is present'
else 'not present'
end

[Lynn]

Unfortunatly It the text in the string somethimes is marked with a captitol like Help or as help. or as helpmeplease
so The wildcards are necessary...

You didn't mention what database you are using, but I'm pretty sure that INSTR for MS Sql and Oracle will return the integer position of the string regardless of what is or isn't around it.

You could also use the "position" function which Cognos would resolve to the appropriate database function depending on your source.

position ( string_expression1 , string_expression2 )

Returns the integer value representing the starting position of "string_expression1" in "string_expression2" or 0 when the "string_expression1" is not found.

Example: position ( 'C' , 'ABCDEF' )

Result: 3

Example: position ( 'H' , 'ABCDEF' )

Result: 0