Position Filter Expresion

[cognos74]

Hi;

I have below example values in Recorded Value column in my query;
Individual;90
Group;120
Co-Treatment;30
Individual;90;60
Individual;Co-Treatment;60

In the report I have to get only below kind of records.  Can any one please let me know that how to get them in the report.
Individual;90;60
Individual;Co-Treatment;60

[hespora]

Well you're giving us precious little here in order to understand what that data is and how we are to determine *why* those two records are the ones you want.

I'm guessing here that it's the fact that there are three segments to the values. Thus, we simply need to identify those rows containing 2 semicolons rather than one. That is done with

Code Select Expand

position(
  ';',
  substring(
    [value],
    position(
      ';',
      [value]
    ) +1
  )
)

> 0

[cognos74]

Thank you very much for your help hespora.  your code worked perfectly for me. 

just for clarification of your question: I need to find those records because someone did not entered the values properly in the database.

[hespora]

you're very welcome; glad I could help!

[cognos74]

Hi hespora,

need little more help with your code.  I need to find below records also along with which rows contain 2 semicolons
(these records does not have semicolon and minutes they are bad records)
Group
Concurrent

please help me.

[hespora]

That would be something like

Code Select Expand


(
position(
  ';',
  substring(
    [value],
    position(
      ';',
      [value]
    ) +1
  )
)

> 0
)

OR

(

position(
  ';',
  [value]
)

=0
)

[cognos74]

Hi hespora,

please let me know how to get only these records:

Individual;90
Group;120

[hespora]

Ahem.... I would *assume* that out of trying to understand what the two solutions do that I've provided you before, you'd be able to build that yourself. I know I personally would have that expectation of myself rather than asking iterations of the same thing over and over. But that's just me. ;)

Anyways, records that have exactly one semicolon:

Code Select Expand


(
position(
  ';',
  [value]
)
>0
)
AND
(
position(
  ';',
  substring(
    [value],
    position(
      ';',
      [value]
    ) +1
  )
)

= 0
)