Re: [sqlite] Is it possible to check a data type in a query?

[Amit Chaudhuri]

Perfect - thank you both..

On Thu, Jul 14, 2011 at 12:15 PM, Simon Slavin  wrote:

>
> On 13 Jul 2011, at 1:13pm, Amit Chaudhuri wrote:
>
> > I have situation where a column may contain prices (REAL) or a string
> > meaning "no price for this, sorry." I can handle this in application code
> > but was wondering if I am overlooking a Sqlite feature which might do the
> > same out of the box: perhaps a TYPE( ) or an IsText( ) function..?
>
> There is a 'typeof'.  But you didn't know the proper way to do it.  What
> you do is set up the column to be a number format (real or integer, however
> you hold your prices) and use NULL when you don't have a price.  Then you
> can very quickly and easily check for a NULL with
>
> typeof(X) = 'null'
> nullif(X,Y)
>
> or convert it to your 'no price for this' string with
>
> ifnull(X,'No price for this, Sorry.')
>
> In fact this is what NULL means in SQL: something like 'data missing' or
> 'value unknown'.  So it is exactly what you really want.  For more details
> see
>
> 
>
> For how to do calculations on numbers that might be NULL, see
>
> 
>
> For more details than you need don't bother seeing
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to check a data type in a query?

[Simon Slavin]

On 13 Jul 2011, at 1:13pm, Amit Chaudhuri wrote:

> I have situation where a column may contain prices (REAL) or a string
> meaning "no price for this, sorry." I can handle this in application code
> but was wondering if I am overlooking a Sqlite feature which might do the
> same out of the box: perhaps a TYPE( ) or an IsText( ) function..?

There is a 'typeof'.  But you didn't know the proper way to do it.  What you do 
is set up the column to be a number format (real or integer, however you hold 
your prices) and use NULL when you don't have a price.  Then you can very 
quickly and easily check for a NULL with

typeof(X) = 'null'
nullif(X,Y)

or convert it to your 'no price for this' string with

ifnull(X,'No price for this, Sorry.')

In fact this is what NULL means in SQL: something like 'data missing' or 'value 
unknown'.  So it is exactly what you really want.  For more details see



For how to do calculations on numbers that might be NULL, see



For more details than you need don't bother seeing



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to check a data type in a query?

[Richard Hipp]

On Wed, Jul 13, 2011 at 8:13 AM, Amit Chaudhuri
wrote:

> Hi all,
>
> I have situation where a column may contain prices (REAL) or a string
> meaning "no price for this, sorry." I can handle this in application code
> but was wondering if I am overlooking a Sqlite feature which might do the
> same out of the box: perhaps a TYPE( ) or an IsText( ) function..?
>

The typeof() function.  http://www.sqlite.org/lang_corefunc.html#typeof


>
> A
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible to check a data type in a query?

[Amit Chaudhuri]

Hi all,

I have situation where a column may contain prices (REAL) or a string
meaning "no price for this, sorry." I can handle this in application code
but was wondering if I am overlooking a Sqlite feature which might do the
same out of the box: perhaps a TYPE( ) or an IsText( ) function..?

A
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users