Re: Wildcard Problem

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Thu, 19 Oct 2006 14:58:06 +0200
Message-ID: <453776a2$0$27624$9b4e6d93@newsspool2.arcor-online.net>

RanLi schrieb:

> harry_johnson_4_you_at_yahoo.com skrev:

>> I tried
>>
>> select *
>> from mydb
>> where length(column1) = 2
>> and substr(column1,1,1) = '4'
>>
>> but it didn't work. It still returned nothing, not even an error.
>>
>> Since this database was created by our vendor, I wasn't exactly sure
>> what the column was. Documentation lists it as char. I tried to do
>> this on a different table and it worked fine.
>>
>> select * from myTable2 where column1 = '36___' pulled exactly what I
>> wanted as well as the other query when I used it on a different table.
>>
>> For some reason, it doesn't work on the table that contains the data I
>> need.

> 
> 
> does a "select * " work?
> The syntax seems ok. It worked on my tables.
> 

As Frank already suspicted, the problem is probably char(n) - i assume it is a char(5) column definition. Shorter than 5 strings are padded to the defined column length with blanks, that is why the query above returns no rows, the condition length(column1)=2 is false for all the rows in the table. It can be workarounded among other with length(trim(column1))=2.

Best regards

Maxim Received on Thu Oct 19 2006 - 07:58:06 CDT