Print Page | Close Window

LIKE Operator using Special Characters

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21148
Printed Date: 04 May 2024 at 1:35am


Topic: LIKE Operator using Special Characters
Posted By: Dr4ke
Subject: LIKE Operator using Special Characters
Date Posted: 03 Nov 2014 at 3:49am
Afternoon,

I currently have two different types of data in my report:

1) {Letter}:\ (E.g. C:\)

2) {Letter}: (E.g. C:)

I want to exclude the first option using "??\" but it throws up a SQL Syntax error.

I've been able to find a workaround for this scenario, using ?:?, but wondered if there was any reason you can't use \ in a LIKE formula?


Replies:
Posted By: DBlank
Date Posted: 03 Nov 2014 at 5:44am

Testing this out I did not get any errors.

What exactly is the formula you are using and where are you placing it?

Posted By: Dr4ke
Date Posted: 03 Nov 2014 at 6:07am
Hi DBlank,

That's strange! The formula used is 

not ({TableName.FieldName} like "??\")


Which then displays two errors. The first one is:

"Failed to retrieve data from the database." and gives the option to click 'Ok'.

Once clicked the following error appears

"Failed to retrieve data from the database. Details: 4200. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "_\')' at line 1."

this only happens when a '\' is at the end of a LIKE clause..

Posted By: DBlank
Date Posted: 03 Nov 2014 at 6:51am
are you using this as your select statement?

Posted By: Dr4ke
Date Posted: 03 Nov 2014 at 8:39am
No this is in the select expert

Posted By: kevlray
Date Posted: 03 Nov 2014 at 12:13pm
I suspect that the translation from Crystal to MySQL syntax is not working correctly (I have seen similar issue before).  You may need to create a stored procedure or command with the 'properly' formatted WHERE clause.

Posted By: DBlank
Date Posted: 03 Nov 2014 at 1:46pm
can it be moved to a crystal formula and then use that result in the select?

Posted By: Dr4ke
Date Posted: 03 Nov 2014 at 9:59pm
Thanks guys.

DBlank - what do you mean regarding the formula? Something like the below? 

IF {field} LIKE ??/ Then "Exclude" Else "Include"
and then use the Select Expert on Exclude?

Posted By: z9962
Date Posted: 03 Nov 2014 at 10:06pm
can you use right({field},1)="\"

Posted By: Dr4ke
Date Posted: 03 Nov 2014 at 10:37pm
That works :-) allowing me to filter on 'True' or 'False'.

Very strange why it wouldn't work in the first place.


Print Page | Close Window