Differences

This shows you the differences between two versions of the page.

--- dev:sql:functions:convert [2009/12/05 01:01]
tdo created
+++ dev:sql:functions:convert [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
 {{page>:top_add&nofooter&noeditbtn}}
-<html><div align="center"><span style="color:red">DRAFT</span></div></html>
-====== Omnidex SQL Function: $CONVERT ======
 {{page>:sql_bar&nofooter&noeditbtn}}
+====== Omnidex SQL Function: $CONVERT ======
 ===== Description =====
-The $CONVERT function converts a scalar_expression from one data type to another of length n. This function may also be used for formatting data.
+The CONVERT function converts an expression to the requested datatype.  This is similar to the CAST function, but allows a wider variety of options.
+This function returns the datatype requested in the function.
-See Also: CAST
 ===== Syntax =====
-$CONVERT(scalar_expression, datatype, n [,options])
-== scalar_expression ==
+  $CONVERT(expression, datatype [, length [, options ]]
-Required. The original data that is to be converted.
+== expression ==
+Required. An expression using SQL language that produces a value. A scalar expression is generally equivalent to a select item in the SQL SELECT statement, and can be a character expression, numeric expression or date expression.
 == datatype ==
+Required.  One of the datatypes listed below:
-Required. A valid Omnidex datatype.
+{{page>dev:appendix:reference:datatypes_insert&noheader&nofooter&noeditbtn}}
-== n ==
+Omnidex also provides partial support for National Character Datatypes (sometimes called Unicode datatypes or wide characters). The Omnidex SQL Engine will process these datatypes, but the Omnidex Indexing Engine will only index the lower 8 bits of these datatypes.
-Required. The length of the newly converted data.
+{{page>dev:appendix:reference:natl_datatypes_insert&noheader&nofooter&noeditbtn}}
+== length ==
+Optional. The length of the datatype is usually specified in the //datatype// parameter; however, if it has not been specified, it may be specified here.  If the length is specified in the //datatype// parameter, this parameter is left empty.
 == options ==
-Optional. Valid options.
+Optional.  Options can be applied to the data conversion to affect the output.  The supported options are:
+^Option  ^Description                                   ^
+|LJ      |Left-justify                                  |
+|RJ      |Right-justify                                 |
+|UPS     |Upshift                                       |
+|DNS     |Downshift                                     |
+|SF      |Space-fill                                    |
+|ZF      |Zero-fill                                     |
+|BWZ     |Blank when zero                               |
 ===== Example =====
-==== Example 1 ====
+==== Example 1: $CONVERT of column ====
+<code>
+> select $convert(individual as integer) ind from individuals;
+IND
+-----------
+  900011022
+  900047761
+  900047762
+  900067115
+  900067114
+  900215567
+  900240828
+</code>
+==== Example 2: $CONVERT with options ====
+<code>
+> select $convert(name, 'char(50)',,'rj dns') name_rj from individuals;
+NAME_RJ
+--------------------------------------------------
+                                    ms. mary lewis
+                             mrs. alice r tolliver
+                             mr. sidney k tolliver
+                           mrs. jennifer j hopkins
+                               mr. charlie hopkins
+                                 ms. kimberly blue
+                                   kathleen tucker
+</code>
+====== Additional Resources ======
+See also:
+{{page>dev:sql:functions:see_also_utility&noheader&nofooter&noeditbtn}}
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

dev/sql/functions/convert.1259974901.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes