How to select values that end in * (asterisk)?

[HaydnCostin]

Hello - using Crystal 10 on MS SQL Server 2000 database

I have tried some of the tips in thread 767-1051284 but I think the key difference is I also want to use wildcards, as follows:

We have order numbers in the form 21064357 and 21064357* where the asterisk is part of the order number string. We sometimes want to select all records where the order number is with an asterisk and sometimes all records where order number is without an asterisk.

I've tried using the literal character chr(42), as follows:

{t_order.order_number} like "*"+chr(42)

but this returned no records. Any help to find the right syntax would be greatly appreicated, thanks!

 

[lbass]

You could try:

replace({t_order.order_number},"*","^") like "*"+"^"

-LB

 

[HaydnCostin]

Thanks, lbass for the prompt reply, but could you just clarify the relevance of the ^ symbol in your formula above? I'm not quite sure of the context in which I should use your proposed formula

 

[lbass]

You could use this as your record selection formula when looking for records that have an asterisk. This formula just replaces the asterisk with a different character--it could be anything that is not ordinarily a part of the order number. Using a formula like this for record selection will slow your report. It would be better if you could make the replacement in a SQL expression--I'm just not sure what function might be available to do that, and it depends upon your particular datasource and connectivity.

-LB

 

[HaydnCostin]

OK, thanks for the feedback, I'll look to use a command instead, I've tried selecting using MS SQL and the logic to select everything where order number like '%*' works fine. Thanks again.

 

[synapsevampire]

Simplify this.

First test if:

right({table.field}) = "*"

is passed to the database by checking Database->Show SQL Query, if so, that'll do nicely

If not, create a SQL Expression of:

Right(table.field,1)

Then use that in the record selection formula, as in:

{%MySQLExpression} ="*"

or without, use:

not({%MySQLExpression} ="*")

This will prove efficient as well. LB's solution will work, but it's inefficient.

-k