Thanks for the quick reply.
This is the create sql of the table PHONE:

CREATE TABLE PHONE(
PHONE_ID INTEGER NOT NULL,
PHONE_NUMBER VARCHAR(100),
UPDATED_DATE INTEGER,
EFFECTIVEFROM INTEGER,
EFFECTIVETO INTEGER,
OBJECTID VARCHAR(25),
MODE SMALLINT,
DEFAULT_EMAIL SMALLINT)

I think I have figured this out now and it was just a display problem to do with
Excel making numerics from text, so not showing a trailing or leading space.


RBS

On 2/24/12, Kjell Rilbe <kjell.rilbe@...> wrote:
> Den 2012-02-24 10:03 skrev Bart Smissaert såhär:
>>
>> FB 1.5 on Windows, classic configuration.
>> Run the query via ODBC and ADO.
>>
>> Have this simple SQL, which is meant to find mobile phone numbers with
>> a space in it:
>>
>> select
>> p.patient_id,
>> ph.phone_number,
>> ph.phone_id
>> from
>> patient p left join
>> phonelink pl on (p.patient_id = pl.foreign_id and
>> pl.phone_type_id in(11, 506) and
>> pl.phonemethodid = 1 and
>> pl.phone_link_type = 2) left join
>> phone ph on (ph.phone_id = pl.phone_id)
>> where
>> p.main_reg_type = 1 and
>> ph.phone_number <> '' and
>> ph.phone_number like '% %'
>> order by
>> ph.phone_number asc
>>
>> It runs fine, but finds numbers with no space in it.
>> Any idea why this is?
>>
>
> My first thought is that ph.phone_number is char(...) instead of
> varchar(...), in which case the SQL will find numbers with trailing
> spaces, i.e. with values shorter than the column width.
>
> If ph.phone_number is in fact varchar(...) I suspect it has to with the
> fact that SQL specifies that for comparison purposes varchar columns are
> right padded with spaces. I thought this only applied to comparisons
> with = and <>, e.g. 'XYZ' = 'XYZ ', but perhaps it also applies to
> like patterns?
>>
>>
> Kjell
>
> --
> --------------------------------------
> Kjell Rilbe
> DataDIA AB
> E-post: kjell@...
> Telefon: 08-761 06 55
> Mobil: 0733-44 24 64
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>