SQL Syntax Problem: LIKE on a column with additional wildcard
Erhard Schwenk wanted to select rows where c1 begins with the value of column c2, but LIKE c2 || '%' did not treat the percent sign as a wildcard. Douglas Wilson suggested comparing c2 with a substring of c1. Rudy Fernandes and another poster showed the cause was CHAR padding and the fix is LIKE TRIM(c2) || '%'. Jonathan Leffler raised operator precedence.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello,
We need to select all values out of a column which begin like the values
in another column.
I tried this one:
select c1 from table where c1 like c2 || '%'
But the problem is that the '%' is not as a wildcard in this
construction.
How is it possible to tell informix to use the value of a field as the
part of a like expression?
On Wed, 06 Sep 2000 08:56:06 +0000, Erhard Schwenk <eschwenk@fto.de> wrote:
>Hello,
>
>We need to select all values out of a column which begin like the values
>in another column.
>I tried this one:
>
>select c1 from table where c1 like c2 || '%'
This is just a guess and completely untested, but:
select c1 from table where c2 = c1[1,length(c2)]
HTH,
Douglas Wilson
You need to TRIM c2. For example,
create table junk (
junk1 char(5),
junk2 char(10)
);
insert into junk values ('abc', 'abcde');
insert into junk values ('abc', 'abc');
insert into junk values ('abc', 'ab');
insert into junk values ('abc', 'a');
select * from junk
where junk1 like TRIM(junk2) || '%';
will return the last 3 rows.
Note that if junk2's definition was VARCHAR(10), TRIM would not be required
in the current example.
Rudy
Erhard Schwenk wrote:
> Hello,
>
> We need to select all values out of a column which begin like the values
> in another column.
> I tried this one:
>
> select c1 from table where c1 like c2 || '%'>
> But the problem is that the '%' is not as a wildcard in this
> construction.
>
> How is it possible to tell informix to use the value of a field as the
> part of a like expression?
Erhard Schwenk wrote:
> We need to select all values out of a column which begin like the values
> in another column. I tried this one:
>
> select c1 from table where c1 like c2 || '%'>
> But the problem is that the '%' is not as a wildcard in this
> construction.
>
> How is it possible to tell informix to use the value of a field as the
> part of a like expression?
Have you checked the precedence of the operators?
Is the condition parsed as:
WHERE c1 LIKE (c2 || '%')
or as:
WHERE (c1 LIKE c1) || '%'
I'm not sure what the second means -- probably concatenate '%' to either
TRUE or FALSE or NULL and if the result is not an empty string...
I'm not sure where to look in TFM to find such a table of precedence.
--
Yours,
Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"
On Wed, 06 Sep 2000 14:33:07 -0700, Jonathan Leffler <jleffler@informix.com>
wrote:
>Erhard Schwenk wrote:
>> We need to select all values out of a column which begin like the values
>> in another column. I tried this one:
>>
>> select c1 from table where c1 like c2 || '%'>>
>> But the problem is that the '%' is not as a wildcard in this
>> construction.
>>
>> How is it possible to tell informix to use the value of a field as the
>> part of a like expression?
>
>Have you checked the precedence of the operators?
>Is the condition parsed as:
>
> WHERE c1 LIKE (c2 || '%')
>
>or as:
>
> WHERE (c1 LIKE c1) || '%'
>
>I'm not sure what the second means -- probably concatenate '%' to either
>TRUE or FALSE or NULL and if the result is not an empty string...
>
>I'm not sure where to look in TFM to find such a table of precedence.
Problem with that WHERE is that concatenaiting column C2 with "%" produce string
with SPACES between value of C2 and "%". For example, if C2 is CHAR(5) and have
value "123":
WHERE c1 like "123 %"
(2 spaces between 3 and %).
You can try with
WHERE c1 LIKE (trim(c2) || '%')
if TRIM() is supported in your version.
Nebojsa