Using LIKE to search for "*" in text 2

[peteriannetta]

Hi there,

I am retrieving records using the LIKE operator on a text field. The pattern I'm searching for is *HIC* (including the asterisks).

So, when I use ...LIKE "*HIC*"... I'm finding text fields that have the word "which" and so forth.

I've also tried "**HIC**" but this also returned any text field that contained just one asterisk, as well as "which" and the rest of it.

Is there a way to search for a literal asterisk in a text field using the LIKE operator? any help would be appreciated. Using CR10.

Cheers,
Pete

 

[crystalvictim]

If you do not need to work with wildcards and just want to make sure that your text field contains the string *HIC*, you might try something like:

if instr({textfield},'*HIC*')>0 then
// found
...
else
// not found
...
end if;

That way, you would find *HIC* while you wouldn't find WHICH.
This works with CR8.5 but I suppose it's still the same with CR10.

 

[MisterMo]

Which database are you using?

Mo

 

[Naith]

Pete,

I don't know what your backend is, but there is an escape character in Sybase and Oracle, for example, which will nullify the characteristics of the next character. e.g. force * to be read as a literal value, instead of a wildcard.

You could utilise a SQL expression to do this for you, as I'm sure Crystal doesn't have it's own escape character functionality.

This additional method will also work; substitute your * with the ascii value and concatenate it to your search criteria.

i.e.

LIKE chr(42)+"HIC"+chr(42)

Let us know if you're still having problems.

Naith

 

[rosemaryl]

Dear pete,

You could create a SQL Expression like this (Expression is named Test here in my example and database is MS SQL Server):

(Case when
len(Table."FieldName") - len(Replace(Table."FieldName",'*HIC*','')) > 0
then 1
else 0
end
)

Now your report select criteria becomes:

{%Test} = 1

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[peteriannetta]

Hi everyone,

Thanks to you all for your prompt replies. I actually went with CrystalVictims suggestion (using the instr() function) and it has worked a treat. But many other interesting solutions to consider and thanks to you all for taking the time.

Cheers,
Pete

 

[rosemaryl]

Dear Pete,

CrystalVictims solution does indeed work. My concern would be that in the selection criteria it will not pass to the database (you can check this by doing a Database/Show SQL Query) thus selecting all the records that meet any other criteria and then having to filter on the resulting recordset.

If performance isn't an issue, his is the easiest way.

Best regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[peteriannetta]

Hi Ro,

Aaah, great point and indeed, query performance could be an issue as the database grows. I've not had experience with SQL expressions but after re-reading your suggestion, will try that when I get back into the office Monday (it's the weekend here in Australia).

Thanks for getting back to me,

Pete