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]