Hi,

becuase you cannot do xx=NULL, you would need xx is null

select count(*) from customer where ascii_val(substring(upper(fname) from 1
for 1 )) is NULL

I expect would work, or

select count(*) from customer where fname is NULL ;

On 1 August 2013 15:30, ASOrg <asorg@...> wrote:

> **
>
>
> Hi Nick
>
> Thank you very much for your reply.
> It seems NULL is the answer, it has 9516 records.
>
> But why is
>
> select count(*) from customer where ascii_val(substring(upper(fname) from
> 1 for 1 ))=NULL ( returns 0 ) ?
>
> Best regards
> Andy
>
>
> --- In firebird-support@yahoogroups.com, Nick Upson <nu@...> wrote:
> >
> > how about
> >
> > select ascii_val(substring(upper(fname) from 1 for 1 )), count(*) from
> > customer group by 1
> >
> > to get a list of the first ascii value and the number of records that
> have
> > that value
> >
> >
> > On 1 August 2013 11:41, Andy Samuel <asorg@...> wrote:
> >
> > > **
>
> > >
> > >
> > >
> > > Dear All
> > > select count(*) from customer ( returns 235384 )
> > >
> > > select count(*) from customer where substring(upper(fname) from 1 for
> 1 )
> > > in ('A','B','C',
> > >
> 'D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
> > > ( returns 187671 )
> > >
> > > so there are 47.713 records whose fname started with letter A-Z.
> > >
> > > select distinct ascii_val(substring(upper(fname) from 1 for 1 )) from
> > > customer where substring(upper(fname) from 1 for 1 ) not in
> > >
> ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
> > > ( returns 0, 13, 46 )
> > >
> > > select count(*) from customer where ascii_val(substring(upper(fname)
> from
> > > 1 for 1 )) in (0,13,46) ( returns 38197 )
> > >
> > > I don't know how to find the other 9516 records.
> > > Help please ?
> > >
> > > Thank you in advance
> > >
> > > [Non-text portions of this message have been removed]
> > >
> > >
> > >
> >
> >
> >
> > --
> > Nick Upson, Telensa Ltd
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>



--
Nick Upson, Telensa Ltd


[Non-text portions of this message have been removed]