D 2013-03-05T20:12:59.281 L 4.1.0\sChanges U sandro W 8212 back to XmlBlob main page

New SQL functions introduced in 4.1.0

Generic SQL functions - type casting

SQLite offers just a very basic type checking; this is sometimes useful, but could be really ennoying very often.
SELECT '1', TypeOf( '1' ), CAST ( '1' AS INTEGER ), TypeOf( CAST ( '1' AS INTEGER ) );

'1'	TypeOf( '1' )	CAST ( '1' AS INTEGER )	TypeOf( CAST ( '1' AS INTEGER ) )
1	text	1	integer

The SQL CAST operator already supports the possibility to explicitly set a data-type; now starting since 4.1.0 SpatiaLite supports few SQL functions performing the same task.
Please note: some subtle differences exists here and there; please carefully read this documentation page. SELECT CastToInteger( 1 ), CastToInteger( 1.4 ), CastToInteger( 1.6 ), CastToInteger( '123' ), CastToInteger( 'alpha' ), CastToInteger ( zeroblob(4) );

CastToInteger( 1 )	CastToInteger( 1.4 )	CastToInteger( 1.6 )	CastToInteger( '123' )	CastToInteger( 'alpha' )	CastToInteger ( zeroblob(4) )
1	1	2	123	NULL	NULL

The CastToInteger() SQL function will attempt to return a value of the Integer data-type, or NULL if no conversion is possible.

an Integer input argument will be returned absolutely unaffected.
a Double input argument will return the nearest Integer value (after applying 4/5 rounding).
a Text input argument will return the corresponding Integer value only if the text string represents a valid number (even a decimal one): if the text string cannot be converted to a number NULL will be returned.
any BLOB input argument will return a NULL value.
any NULL input argument will return a NULL value.

SELECT CastToDouble( 1 ), CastToDouble( 1.23 ), CastToDouble( '123.45' ), CastToDouble( 'alpha' ), CastToDouble ( zeroblob(4) );

CastToDouble( 1 )	CastToDouble( 1.23 )	CastToDouble( '123.45' )	CastToDouble( 'alpha' )	CastToDouble ( zeroblob(4) )
1.000000	1.230000	123.45000	NULL	NULL

The CastToDouble() SQL function will attempt to return a value of the Double data-type, or NULL if no conversion is possible.

an Integer input argument will return the corresponding Double value.
a Double input argument will be returned absolutely unaffected.
a Text input argument will return the corresponding Double value only if the text string represents a valid number: if the text string cannot be converted to a number NULL will be returned.
any BLOB input argument will return a NULL value.
any NULL input argument will return a NULL value.

SELECT CastToText( 1 ), CastToText( 1.5 ), CastToText( 'alpha' ), CastToText ( zeroblob(4) );

CastToText( 1 )	CastToText( 1.5 )	CastToText( 'alpha' )	CastToText ( zeroblob(4) )
1	1.5	alpha	NULL

The CastToText() SQL function will attempt to return a value of the Text data-type, or NULL if no conversion is possible.

an Integer input argument will return the corresponding Text value.
a Double input argument will be return the corresponding Text value; no significative decimal digit will be suppressed.
a Text input argument will return the corresponding Double value.
any BLOB input argument will return a NULL value.
any NULL input argument will return a NULL value.

SELECT CastToText( 1, 4 ), CastToText( 1234, 2 ), CastToText( 1, 8 ), CastToText( 1.5, 4 );

CastToText( 1, 4 )	CastToText( 1234, 2 )	CastToText( 1, 8 )	CastToText( 1.5, 4 )
0001	1234	00000001	0001.5

An overloaded version of CastToText() is supported as well, but only when the input argument is of the Integer or Double data-type: in this case a second Integer argument could be optionally specified, and will be interpretered ad zero-padding-length.
i.e. the returned Text string in this case will have an integer part at least of the specified length, and eventually padded by inserting not significative leading ZEROes.

SELECT CastToBlob( 1 ), CastToBlob( 1.5 ), Hex( CastToBlob( 'alpha' ) ), Hex ( CastToBlob ( zeroblob(4) ) );

CastToBlob( 1 )	CastToBlob( 1.5 )	Hex( CastToBlob( 'alpha' ) )	Hex( CastToText ( zeroblob(4) ) )
NULL	NULL	616C706861	00000000

The CastToBlob() SQL function will attempt to return a value of the BLOB data-type, or NULL if no conversion is possible.

an Integer input argument will return a NULL value.
a Double input argument will return a NULL value.
a Text input argument will return the corresponding BLOB value.
any BLOB input argument will return the corresponding BLOB value.
any NULL input argument will return a NULL value.

SELECT ForceAsNull( 'a', 'b' ), ForceAsNull( 'abcd', 'abcd' ), ForceAsNull( 1, 1), ForceAsNull( 1.1, 1 );

ForceAsNull( 'a', 'b' )	ForceAsNull( 'abcd', 'abcd' )	ForceAsNull( 1, 1)	ForceAsNull( 1.1, -9999 )
a	NULL	NULL	1.1

The ForceAsNull() SQL function will evaluate two arbitrary arguments.
If they are equal and exactely of the same data-type NULL will be returned; otherwise the first argument value will be returned.
Useful e.g. while processing data imported from any Shapefile or DBF file (such formats doesn't support any real NULL value, often being replaced by some conventional value e.g. 0 or -9999 and alike).

Generic SQL function - UUID generator

An Universally unique identifier (aka UUID) simply is a 128 bit Integer; accordingly to theory, the probability that two randomly generated UUIDs could assume the same value is practically near zero. And consequently UUIDs are widely used as universally unique IDs.
Usually UUIDs are represented in their canonical form, i.e. as a sequence of 32 hexadecimal digits separeted into five distinct block, accordingly to a 8-4-4-4-12 schema. SELECT CreateUUID(); -------- fff5faf1-dcc0-4391-8054-6e7de75d85b1 The CreateUUID() SQL function is built on the top the High Quality Pseudo-Random Number Generator internally implemented by SQLite.
The returned UUID is conformant to Version 4 (random) (i.e. the first digit into the third block will always be 4 and the first digit into the fourth block will always be 8).

back to XmlBlob main page Z ea36518d8229d128faf6cf80b7a94a67