CODEWITHSUNDEEP
sqloracle10gcognos
Btibert3
Btibert3

Reputation: 40136

Instr Function Cognos Report

I am trying to find the @ in a string field. The follow data item in a Congos 8 returns 

0

for every call.

instr 
(
[email protected], '@', 1
)

Any ideas? New to Cognos and Oracle in general but I feel like I have to be missing something obvious.

Upvotes: 2

Views: 4639

Answers (2)

rossdavidh
rossdavidh

Reputation: 1996

If you just want to know if there is an @ sign in there, you can do something like this (here as it would appear in a filter):

[Business View].[Student Demographics (by Semester)].[Student Name] CONTAINS 'Z'

If you want the position of the @, as a value to do something with, you can do something like this (here as it would appear in a Data Item Expression):

position( 'Z', [Business View].[Student Demographics (by Semester)].[Student Name] )

Note that the value you're looking for needs to appear first, the string you're looking in second.

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881293

Does Cognos even have an InStr? I've always used Position for this.

A little more digging reveals InStr as an Oracle function but I'm not sure on the interplay between the database and Cognos since we just use the database for getting data, all user manipulations of the data are handled purely on the Cognos side for us.

But, even if that worked properly, it seems to me that your first argument should be quoted as well, yes? It is supposed to be a string after all.

Upvotes: 2

Related Questions