Hi All,

I was attempting to escape a wildcard character in a query using a character list:

select diagnet,stage from diag
where stage like ('%[_]%')

I also tried the ESCAPE clause:

select diagnet,stage from diag
where stage like ('%_%') ESCAPE '_'

The data does contain substages (ex: III_A III_B) but the result set is empty.
by removing the escape clause in the second attempt the full data set is returned including those lacking an underbar. Is my syntax correct?

This was run in dbsys 4.49b1 and it's not mission critical. I'm just trying to learn something (besides the avoidance of wildcards in data definitions!).

Thanks in advance and be well,
Jim

On 9/2/2020 11:14 AM, Shedden wrote:
> I was attempting to escape a wildcard character in a query using a character list:
>
> select diagnet,stage from diag
> where stage like ('%[_]%')
>
> I also tried the ESCAPE clause:
>
> select diagnet,stage from diag
> where stage like ('%_%') ESCAPE '_'
>
> The data does contain substages (ex: III_A III_B) but the result set is empty.
> by removing the escape clause in the second attempt the full data set is returned including those lacking an underbar. Is my syntax correct?
>
> This was run in dbsys 4.49b1 and it's not mission critical. I'm just trying to learn something (besides the avoidance of wildcards in data definitions!).
>
> Thanks in advance and be well,

You can specify the escape char in the query - something like this
should work :

select diagnet,stage from diag where stage like '%[_%' ESCAPE '['


Raul

"_________
You can specify the escape char in the query - something like this
should work :

select diagnet,stage from diag where stage like '%[_%' ESCAPE '['
___________"

Hi Raul,

Thanks for the reply.

I'm trying to escape the under bar '_' . The syntax in my second attempt tries to use the ESCAPE clause as you suggest. Was I incorrect using the syntax? I don't need to escape the left bracket. There are none in the data definitions for "stage".

All the Best,
Jim

Hi Raul,

I may understand now. I just tried your suggestion and it returned what I expect. Is it true that the ESCAPE clause defines the character used to 'escape' the following wildcard instead of specifying which wildcard to ignore? I can see a greater utility in that.

Thanks!
Jim

On 9/2/2020 4:39 PM, Shedden wrote:
>
> I may understand now. I just tried your suggestion and it returned what I expect. Is it true that the ESCAPE clause defines the character used to 'escape' the following wildcard instead of specifying which wildcard to ignore? I can see a greater utility in that.
>

Jim

Correct - online manual described it better than i can here

https://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=rsdelphiwin32&version=10.4&topic=Operators


"The ESCAPE keyword can be used after the comparison to represent an
escape character in the comparison value. When an escape character is
found in the comparison value, DBISAM will treat the next character
after the escape character as a literal and not a wildcard character"


I used [ simply as a sample but of course anything else not reserved is
OK so just choose one that works and is meaningful.

Raul