Table.Select not work with '*' in middle of query...

  • Thread starter Thread starter VM
  • Start date Start date
V

VM

If I want to run this query ( " col_output like '<del> *BOX' " ), it'll
throw an exception. How can I search for any rows that begin with '<del>'
and end with 'BOX'?
Would it also be possible to retrieve any rows that begin with '*<del>' and
end with 'BOX*' (ie. "col_output like '*<del>*BOX*' " ) ?

Thanks.
 
VM said:
If I want to run this query ( " col_output like '<del> *BOX' " ), it'll
throw an exception. How can I search for any rows that begin with '<del>'
and end with 'BOX'?
Would it also be possible to retrieve any rows that begin with '*<del>' and
end with 'BOX*' (ie. "col_output like '*<del>*BOX*' " ) ?

Thanks.
Click to expand...


I'm not entirely sure, but you might try using the percent symbol (%) in
place of the asterisks (*).

"col_output like '<del> %BOX' "


Rick Sawtell
 
I tried that but it doesn't work either.

VM

Rick Sawtell said:
I'm not entirely sure, but you might try using the percent symbol (%) in
place of the asterisks (*).

"col_output like '<del> %BOX' "


Rick Sawtell
Click to expand...
 
VM said:
If I want to run this query ( " col_output like '<del> *BOX' " ), it'll
throw an exception. How can I search for any rows that begin with '<del>'
and end with 'BOX'?
Click to expand...

Have you tried:

col_output like '<del>*' AND col_output like '*BOX'

?

The reason your previous attempt didn't work is that, as the docs
state:

<quote>
Wildcards are not allowed in the middle of a string. For example,
'te*xt' is not allowed.
</quote>
 
It wouldn't work because then it'd return this string:
<del> BELLEVUE STREET \r\n
<czz> BOXVILLE MA 10029-2293

The query must only return something like this:
<del> PO BOX 19930 \r\n
<czz> HOUSTON TX 10029-2293
 
VM said:
It wouldn't work because then it'd return this string:
<del> BELLEVUE STREET \r\n
<czz> BOXVILLE MA 10029-2293

The query must only return something like this:
<del> PO BOX 19930 \r\n
<czz> HOUSTON TX 10029-2293
Click to expand...

Well the first would match '<del> *BOX' if you could do that anyway, as
the * would match "BELLEVUE STREET \r\n<czz> " wouldn't it?

What do you *really* want it to do?
 
In my table, I have two records and in col_output, the 2 string values are:

<del> BELLEVUE STREET
<csz> BOXVILLE MA 10029-2293
---and---

<del> PO BOX 19930 \r\n
<czz> HOUSTON TX 10029-2293

I want to retrieve the second record with only the string 'BOX' available
create the query. But you're right because '<del> * BOX*' would also bring
the first one.

I know there's a better way to store the data but for now the client wants
it like this.
 
VM said:
In my table, I have two records and in col_output, the 2 string values are:

<del> BELLEVUE STREET
<csz> BOXVILLE MA 10029-2293
---and---

<del> PO BOX 19930 \r\n
<czz> HOUSTON TX 10029-2293

I want to retrieve the second record with only the string 'BOX' available
create the query. But you're right because '<del> * BOX*' would also bring
the first one.
Click to expand...

So what is it that makes the second different from the first for you?
Is it that BOX is on its own? Do you actually want <del> * BOX *? If
so, just do

foo LIKE '<del > * ' and foo LIKE '* BOX *'
 
If " col_output like '<del> *BOX' " is part a SQL string
Then you should use % for zero or more chars, use _ for single char.

Lishi Liu, VSData Team
--------------------
 
Back
Top