D 2013-03-05T20:22:04.884
L 4.1.0\sChanges
P a9026b69b18db48664908bb5d85c3636887fce9c
U sandro
W 8317
back to XmlBlob <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=XmlBlob+and+VirtualXPath">main page</a>
<h2>New SQL functions introduced in 4.1.0</h2>
<h3>Generic SQL functions - type casting</h3>
SQLite offers just a very basic type checking; this is sometimes useful, but could be really ennoying very often.<br>
<verbatim>
SELECT '1', TypeOf( '1' ), CAST ( '1' AS INTEGER ), TypeOf( CAST ( '1' AS INTEGER ) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>'1'</th><th>TypeOf( '1' )</th><th>CAST ( '1' AS INTEGER )</th><th>TypeOf( CAST ( '1' AS INTEGER ) )</th></tr>
<tr><td>1</td><td>text</td><td align="right">1</td><td>integer</td></tr></table><br>
The SQL <b>CAST</b> 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.<br>
<u>Please note</u>: some subtle differences exists here and there; please carefully read this documentation page.
<verbatim>
SELECT CastToInteger( 1 ), CastToInteger( 1.4 ), CastToInteger( 1.6 ),
CastToInteger( '123' ), CastToInteger( 'alpha' ), CastToInteger ( zeroblob(4) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToInteger( 1 )</th><th>CastToInteger( 1.4 )</th><th>CastToInteger( 1.6 )</th><th>CastToInteger( '123' )</th><th>CastToInteger( 'alpha' )</th><th>CastToInteger ( zeroblob(4) )</th></tr>
<tr><td align="right">1</td><td align="right">1</td><td align="right">2</td><td align="right">123</td><td>NULL</td><td>NULL</td></tr></table><br>
The <b>CastToInteger()</b> SQL function will attempt to return a value of the <b>Integer</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will be returned absolutely unaffected.</li>
<li>a Double input argument will return the <u>nearest</u> Integer value (after applying <b>4/5</b> rounding).</li>
<li>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.</li>
<li>any BLOB input argument will return a NULL value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul>
<hr>
<verbatim>
SELECT CastToDouble( 1 ), CastToDouble( 1.23 ), CastToDouble( '123.45' ),
CastToDouble( 'alpha' ), CastToDouble ( zeroblob(4) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToDouble( 1 )</th><th>CastToDouble( 1.23 )</th><th>CastToDouble( '123.45' )</th><th>CastToDouble( 'alpha' )</th><th>CastToDouble ( zeroblob(4) )</th></tr>
<tr><td align="right">1.000000</td><td align="right">1.230000</td><td align="right">123.45000</td><td>NULL</td><td>NULL</td></tr></table><br>
The <b>CastToDouble()</b> SQL function will attempt to return a value of the <b>Double</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will return the corresponding Double value.</li>
<li>a Double input argument will be returned absolutely unaffected.</li>
<li>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.</li>
<li>any BLOB input argument will return a NULL value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul>
<hr>
<verbatim>
SELECT CastToText( 1 ), CastToText( 1.5 ), CastToText( 'alpha' ), CastToText ( zeroblob(4) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToText( 1 )</th><th>CastToText( 1.5 )</th><th>CastToText( 'alpha' )</th><th>CastToText ( zeroblob(4) )</th></tr>
<tr><td>1</td><td>1.5</td><td>alpha</td><td>NULL</td></tr></table><br>
The <b>CastToText()</b> SQL function will attempt to return a value of the <b>Text</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will return the corresponding Text value.</li>
<li>a Double input argument will be return the corresponding Text value; no significative decimal digit will be suppressed.</li>
<li>a Text input argument will return the corresponding Double value.</li>
<li>any BLOB input argument will return a NULL value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul>
<verbatim>
SELECT CastToText( 1, 4 ), CastToText( 1234, 2 ), CastToText( 1, 8 ), CastToText( 1.5, 4 );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToText( 1, 4 )</th><th>CastToText( 1234, 2 )</th><th>CastToText( 1, 8 )</th><th>CastToText( 1.5, 4 )</th></tr>
<tr><td>0001</td><td>1234</td><td>00000001</td><td>0001.5</td></tr></table><br>
An overloaded version of <b>CastToText()</b> 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 <b>zero-padding-length</b>.<br>
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.<br>
<hr>
<verbatim>
SELECT CastToBlob( 1 ), CastToBlob( 1.5 ), Hex( CastToBlob( 'alpha' ) ), Hex ( CastToBlob ( zeroblob(4) ) );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>CastToBlob( 1 )</th><th>CastToBlob( 1.5 )</th><th>Hex( CastToBlob( 'alpha' ) )</th><th>Hex( CastToText ( zeroblob(4) ) )</th></tr>
<tr><td>NULL</td><td>NULL</td><td>616C706861</td><td>00000000</td></tr></table><br>
The <b>CastToBlob()</b> SQL function will attempt to return a value of the <b>BLOB</b> data-type, or <b>NULL</b> if no conversion is possible.
<ul>
<li>an Integer input argument will return a NULL value.</li>
<li>a Double input argument will return a NULL value.</li>
<li>a Text input argument will return the corresponding BLOB value.</li>
<li>any BLOB input argument will return the corresponding BLOB value.</li>
<li>any NULL input argument will return a NULL value.</li>
</ul>
<hr>
<verbatim>
SELECT ForceAsNull( 'a', 'b' ), ForceAsNull( 'abcd', 'abcd' ), ForceAsNull( 1, 1), ForceAsNull( 1.1, 1 );
</verbatim>
<table border="1" cellspacing="4" cellpadding="4">
<tr><th>ForceAsNull( 'a', 'b' )</th><th>ForceAsNull( 'abcd', 'abcd' )</th><th>ForceAsNull( 1, 1)</th><th>ForceAsNull( 1.1, -9999 )</th></tr>
<tr><td>a</td><td>NULL</td><td>NULL</td><td align="right">1.1</td></tr></table><br>
The <b>ForceAsNull()</b> SQL function will evaluate two arbitrary arguments.<br>
If they are equal and exactely of the same data-type NULL will be returned; otherwise the first argument value will be returned.<br>
Useful e.g. while processing data imported from any <b>Shapefile</b> or <b>DBF file</b> (such formats doesn't support any real NULL value, often being replaced by some conventional value e.g. <b>0</b> or <b>-9999</b> and alike).
<hr>
<h3>Generic SQL function - UUID generator</h3>
An <a href="http://en.wikipedia.org/wiki/Universally_unique_identifier">Universally unique identifier</a> (aka <b>UUID</b>) simply is a <u>128 bit</u> Integer; accordingly to <a href="http://en.wikipedia.org/wiki/UUID#Random_UUID_probability_of_duplicates">theory</a>, the probability that two randomly generated UUIDs could assume the same value is near to zero for many practical purposes. And consequently UUIDs are widely used as <u>universally unique IDs</u>.<br>
Usually UUIDs are represented in their <u>canonical form</u>, i.e. as a sequence of 32 hexadecimal digits separeted into five distinct block, accordingly to a <b>8-4-4-4-12</b> schema.
<verbatim>
SELECT CreateUUID();
--------
fff5faf1-dcc0-4391-8054-6e7de75d85b1
</verbatim>
The <b>CreateUUID()</b> SQL function is built on the top the <i>High Quality Pseudo-Random Number Generator</i> <a href="http://www.sqlite.org/c3ref/randomness.html">internally implemented</a> by SQLite.<br>
The returned UUID is conformant to <u>Version 4 (random)</u> (i.e. the first digit into the third block will always be <b>4</b> and the first digit into the fourth block will always be <b>8</b>).
<hr>
back to XmlBlob <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=XmlBlob+and+VirtualXPath">main page</a>
Z 8ab12e896af54b978dad89b5c9ad8950