A formula to pull data with certain character combinations?

[pokerace]

Using Crystal 10 with an ODBC DB.

I have a formula field that pulls data codes from a field and then converts the codes to easier to understand strings on the report. It looks like this (to simplify):

If {table.field} startswith "S-ABC" then "ABC" else if
{table.field} startswith "S-DEF" then "DEF" else if
{table.field} startswith "S-GHI" then "GHI" else if
etc...

Now some of these codes are broken down further to track locations via a two letter combination somewhere in the string, but not always in the same location.

For example, where the location identifer is YB I might have data like:
S-ABCYB123 or
S-ABCAAYB123 or
S-ABC1YB123
etc...

What I want to do is put in a formula after each if statement that will look for the YB anywhere in the string so that if the data code is "S-ABC1YB123" or "S-ABCAAYB123" then it will appear on the report as "ABC LocationYB"

Something like this could work:
else if
{table.field} startswith "S-ABC" then
(look for YB and if found print "ABC Location Main"

 

[lbass]

You could use something like:

if {table.field} startswith "S-ABC" then
if instr({table.field},"YB") > 0 then
"ABC Location Main"

-LB

 

[IanWaterman]

You can use the Instr function.

if
({table.field} startswith "S-ABC" then
if instr({table.field}, "YB") <> 0 then "ABC Location Main"
else "ABC")
Else
......

The logic for this is based upon
InStr("S-ABCAAYB123", "YB")
Returns 8

as YB starts at chracter 8

InStr"S-ABCAA123", "YB")
Returns 0

Ian

 

[pokerace]

Thanks!