Print Page | Close Window

Selection format for 'LIKE' with ESCAPE

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12802
Printed Date: 08 May 2024 at 2:25pm


Topic: Selection format for 'LIKE' with ESCAPE
Posted By: blapath
Subject: Selection format for 'LIKE' with ESCAPE
Date Posted: 04 Apr 2011 at 10:46pm
In standard SQL I can define a condition with the LIKE operation, defining the character to be used as an escape character (when in the pattern wild card character should be served not as wild card). For instance, if I am looking for a record where an field value starts with ABC%, the actual SQL condition will be ... WHERE TBL.ATTR LIKE 'ABC!%$' ESCAPE '!'.

In CR, the * character serves as the 'any char' wild card (instead of %), so logically a condition for looking for ABC* should defined as ... WHERE {TBL.ATTR} LIKE 'ABC!**' ESCAPE '!'.

Unfortunately, CR rejects this format and I don't find what the actual format should be. Can anyone help me out?
Thanks in advance!


Replies:
Posted By: lockwelle
Date Posted: 05 Apr 2011 at 7:35am
hmmm,
so your problem, as I understand it is how to find 'ABC%', since you are looking for a way to find the special character %, but for CR, I would think, it would be just LIKE 'ABC%*', since it doesn't recognize the % as having a special meaning.  In this way CR syntax mimics the Access formatting.
 
If you are looking for a string that starts 'ABC*' in CR, how can we get around this...How about trying CHARINDEX('ABC', {table.field}) = 1 AND CHARINDEX('*', {table.field}) = 4, though in all honesty, I don't if that would be correct for CR syntax. 
 
Personally, I found dealing with CR SQL syntax too much of a pain, way back in CR 7, so I just write stored procs to get the data, and call them from CR.  Much more versatile and does have frustrations like this.
 
HTH

Posted By: blapath
Date Posted: 05 Apr 2011 at 9:26pm
Thanks for your quick answer. It helps and not. At least I know I didn't miss something obvious.
 
I assume the suggested CR syntax can work but probably an SP based solution can be better, as far as you have a fixed query/condition. Unfortunately it is not my case. The problem is  that the condition is defined by user, both for attributes and conditions and values. As such the  SP should be built dynamically in runtime, or at least fed with the dynamic condition.
 
Nevertheless, it is a direction I cna start investigate.
Thanks again.


Print Page | Close Window