Crystal Reports LIKE [a-z][0-9][a-z][a-e]

Question

I'm creating a report to select records in a table where a string field matches a certain pattern.

The records that I need are displayed in a grid, so I can see that there are around 50 records that should be getting selected. The query to get the records from Access uses WHERE Product_Description_Short LIKE '[a-z][0-9][a-z][a-e]'

When I then try to put this into a Crystal selection formula, no records are returned.

"{Product_Costs.Product_Description_Short} LIKE '[a-z][0-9][a-z][a-e]'"

How do I need to adapt the selection formula to allow me to select the same records as in the SQL query?

Removing the selection formula completely shows all records from the table, so it's definitely the query going wrong.

Pattern matching a range might not be simple, review http://kenhamady.com/formulas/form07.shtml — June7, Mar 06 '18 at 20:56
If your SQL query works from Access why not use that in a Command Table rather than having Crystal do it? — CoSpringsGuy, Mar 06 '18 at 21:33

MatSnow · Accepted Answer · 2018-03-07T15:53:18.790

1

Crystal Reports does not support regex, but you can use some string functions to achieve what you want.
Also there was a similar question with a link to a regex-library:
Crystal reports: is there a way to regex in crystal reports?

The following formulas use some of the basic string functions of Crystal Reports:

Example 1:

This formula works with character ranges. The problem here is that also other characters are included (for example umlauts like ä ö ü). If this is not what you want then "Example 2" should work.

stringVar productDesc := "a3xZ";

Len(productDesc)=4 And
Left(productDesc,1) In "a" To "z" And
Mid(productDesc,2,1) In "0" To "9" And
Mid(productDesc,3,1) In "a" To "z" And
Right(productDesc,1) In "a" To "z"

Example 2:

This formula works with ASCII-codes and thus can exclude umlaut-characters.

ASCII-codes 48 to 57 are numbers [0-9]
65 to 90 are upper case characters [A-Z]
97 to 122 are lower case characters [a-z]

stringVar productDesc := "a3xZ";

Len(productDesc)=4 And
Asc(Left(productDesc,1)) In [97 To 122, 65 To 90] And
Asc(Mid(productDesc,2,1)) In 48 To 57 And
Asc(Mid(productDesc,3,1)) In [97 To 122, 65 To 90] And
Asc(Right(productDesc,1)) In [97 To 122, 65 To 90]

So with the second example, the selection formula would look like this:

Len({Product_Costs.Product_Description_Short})=4 And
Asc(Left({Product_Costs.Product_Description_Short},1)) In [97 To 122, 65 To 90] And
Asc(Mid({Product_Costs.Product_Description_Short},2,1)) In [97 To 122, 65 To 90] And
Asc(Mid({Product_Costs.Product_Description_Short},3,1)) In 48 To 57 And
Asc(Right({Product_Costs.Product_Description_Short},1)) In [97 To 122, 65 To 90]

edited Mar 07 '18 at 15:53

answered Mar 07 '18 at 08:22

MatSnow

7,357
3
19
31

Hi Mat. Would this go in the selection formula or would this be a parameter/formula field? Secondly, would I need to replace "ax32" on the top line to be `productDesc := {MyField}` or leave it as ax32? – David Mar 07 '18 at 09:35
@David I've edited the answer and added an example of the selection formula. I've used the variable just to show an example value. – MatSnow Mar 07 '18 at 09:50
The `Len` part is working fine. When I add in the next line, `Asc(Left...`it returns 0 results. The first record for example beings with a C, but the report isn't selecting it? – David Mar 07 '18 at 12:07
It definitely should work. Put the whole code in a formula-field and add that field to the report and remove the selection formula. The formula-field should show the value `True` if the `Product_Costs.Product_Description_Short` fits the pattern. If not, then please add some example data to your question. – MatSnow Mar 07 '18 at 12:24
I think I see why it's not working for you. I've mistakenly switched the check for the second and third char in my examples. Sorry for this...it's corrected now. – MatSnow Mar 07 '18 at 15:55
1

Yeah that was the issue, I noticed that when adding example data to the question. All working now, cheers. – David Mar 07 '18 at 16:42

Crystal Reports LIKE [a-z][0-9][a-z][a-e]

1 Answers1