Instr Function

[cbochner]

Hi,

Is there a feature that you can find a position in a string based on a string search? Fo instance:

Email: john.doe@yahoo.com

Is there a function I can use in cognos to retrieve the "com" from the string?

Please enlighten me.

Thank You

C.B.

 

[drlex]

The function you require may depend on:
1) cognos product you're using
2) the database you're interrogating.
(The second point is more relevant to speed of query.)

soi la, soi carré

 

[blom0344]

sql server:

CASE WHEN CHARINDEX('COM',[STRING]) = 0
THEN '---'
WHEN CHARINDEX('COM',[STRING]) > 0
THEN 'COM' ELSE NULL

oracle:

CASE WHEN INSTR([STRING],'COM') = 0
THEN '---'
WHEN INSTR([STRING],'COM') > 0
THEN 'COM' ELSE NULL

db2:

CASE WHEN POSSTR([STRING],'COM') = 0
THEN '---'
WHEN POSSTR([STRING],'COM') > 0
THEN 'COM' ELSE NULL

Ties Blom

 

[cbochner]

Hi Again,

Is there a capability in ReportNet 8 to program? For instance, I would like to find the last "." in an e-mail address, so I can grab the last part of the e-mail address and present it into a drop down list.

Can such a thing be done?

Please Help.

Chaim Bochner

 

[blom0344]

I doubt very much that Cognos has this kind of functionality. It should be possible with common database functions (using some nested expressions)
It all boils down to which database you are actually addressing. If we know which one you are using to base your report on then we can take it from there.

Ties Blom

 

[blom0344]

When using SQL server the following expression does the job:

SUBSTRING([STRING], LEN([STRING]) + 2 - CHARINDEX('.', REVERSE([STRING])), CHARINDEX('.', 
                      REVERSE([STRING])))

However, I do not know if the 'REVERSE' function is availabele in the Cognos SQL server palette. It is only available in SQL server.

The expression works for varchar fields. (add trim for char fields)

Ties Blom