StelsMDB SQL Syntax

StelsMDB JDBC driver supports the following SQL statements and syntax:

SELECT

INSERT

UPDATE

DELETE

CREATE TABLE

query:

SELECT [ TOP n [ PERCENT ]] [ ALL | DISTINCT ] select_list
FROM table_reference_list
[ WHERE condition_expression ]
[ GROUP BY column [ , ... ] ]
[ HAVING condition_expression [ , ... ] ]
[ union_clause ]

[ ORDER BY order_expression [ , ... ] ]

select_list:

{ { expression [ [ AS ] column_alias ] | table_name.* } [ , ... ] } | *

table_reference_list:

{ table_reference | table_join }

table_reference:

{ table_name | subquery } [ [ AS ] table_alias ]

table_name: { string | "string" | !string! | `string` }

table_alias: { string | 'string' | "string" | !string! | `string` }

subquery: ( query )

table_join : table_reference join_clause [ join_clause, ... ]

join_clause:

[ INNER | { { LEFT | RIGHT | FULL } OUTER } ] JOIN table_reference ON join_expression

join_expression:

column = column [ AND join_expression ]

union_clause:

UNION [ ALL ] query [ union_clause, ... ]

expression:
{ value | COUNT( * ) | aggregate_function } [ [ AS ] column_alias ]

aggregate_function:

{ COUNT | MIN | MAX | SUM | AVG } ( [ DISTINCT ] column )

column_alias: { string | 'string' | "string" | !string! | `string` }

condition_expression:

[ NOT ] condition [ { OR | AND } condition_expression ]

condition:
value { = | < | <= | > | >= | <> } { value | subquery }
| value BETWEEN value AND value
| value [ NOT ] LIKE pattern
| value [ NOT ] IN ( value [, ...] )

| value [ NOT ] IN subquery

| value IS [ NOT ] NULL

orderExpression:
column [ ASC | DESC ]

value:

constant

| column
| function ( [ value ] [ ,... ] )
| [ - ] value [ + | - | * | / value ]

constant: { 'string' | integer | long | float | double | NULL }

column: [ table_alias. ] { string | "string" | !string! | `string` }

Notes:

- The driver uses only the ANSI SQL'92 notation for joins, e.g.: SELECT * FROM prices ps JOIN regions regs ON ps.regionid = regs.id JOIN products prod ON prod.prodid = ps.prodid

- The driver supports non-correlated subqueries only.

- LIKE p attern can include the following valid SQL wildcard characters: '%' - any string of zero or more characters, '_' - any single characters

INSERT INTO tableName [ ( column [,...] ) ]
{ VALUES ( value [,...] ) | selectStatement }

UPDATE tableName SET column =

value [, ...]
[ WHERE whereExpression ]

DELETE FROM tableName

[ WHERE whereExpression ]

CREATE TABLE tableName

( column datatype [, ...] )

functions:

Conversion functions

Name | Description | Example
to_string(arg : any type) : string type | converts an argument to a string value | to_string(120), to_string(1.587), to_string(float_column), to_string(date_column)
to_string(arg : integer | long | float | double | decimal type, format : string type) : string type | converts an argument to a string value with the specified number format. | to_string(123467.8, '###,###.##'), to_string(123467.8, '000000.000')
to_string(arg : integer | long | float | double | decimal type, format : string type, isoLang : string type, isoCountry : string type) : string type | converts an argument to a string value with the specified number format, ISO language code and ISO country code | to_string(123467.8, '###,###.##', 'en', 'US'), to_string(123467.8, '000000.000', 'de', 'DE')
to_string(arg : datetime type, format : string type) : string type | formats the given argument into a date/time string with the specified format | to_string(date_column, 'yyyy-MM-dd HH:mm:ss')
to_string(arg : datetime type, format : string type, isoLang : string type, isoCountry: string type) : string type | formats the given argument into a date/time string with the specified format, ISO language code and ISO country code | to_string(date_column, 'MMMMM yyyy', 'en', 'US')
to_int(arg : any type) : integer type | converts an argument into a integer value | to_int(1.578), to_int('457')
to_long(arg : any type) : long type | converts an argument into a long value | to_long(1.578), to_long('123456789012')
to_float(arg: any type) : float type | converts an argument into an floating-point value | to_float(345), to_float('234.57')
to_double(arg : any type) : double type | converts an argument into a double value | to_double(1.578), to_double('1234567890.1234')
to_big_decimal(arg : any type) : decimal type | converts an argument into a java.math.BigDecimal value | to_big_decimal(1.578), to_big_decimal('1234567890.1234')
to_date(arg: string type, format : string type) : datetime type * | converts an argument into a date/time value with the specified format | to_date('2003-12-25','yyyy-MM-dd')
to_date(arg: string type, format : string type, isoLang : string type, isoCountry : string type) : datetime type * | converts an argument into a date/time value with the specified format, ISO language code and ISO country code | to_date('September 2003', 'MMMMM yyyy', 'en', 'US')

* The driver also supports the JDBC escape syntax: {d 'yyyy-mm-dd'}, {t 'hh:mm:ss'}, {ts 'yyyy-mm-dd hh:mm:ss'} for converting a string argument into a date/time value.

String functions

Name | Description | Example
index_of(arg : string type, searchstr : string type) : integer type | Returns the index location of the first occurrence of the specified string. | index_of(str_column, 'John')
last_index_of(arg : string type, searchstr : string type) : integer type | Returns the index location of the last occurrence of the specified string. | last_index_of(str_column, 'John')
length(arg : string type) : integer type | returns the number of characters in the string. | length(str_column + 'qwerty')
lower(arg : string type) : string type | converts all of the characters in the string to lower case | lower('ABCDE')
matches(arg : string type, regex : string type) : string type | attempts to match the given argument against the specified regular expression. The function returns 1 if the argument is matched and returns 0 in otherwise. | matches('A1', '[a-zA-Z][0-9]')
replace_string(arg : string type, replaceable : string type, replacement : string type) : string type | replaces each substring of specified argument with the given replacement | replace_string(str_column, ',' , '.')
sub_string(arg : string type, beginIndex : integer type) : string type | returns a string that is a substring of this string | substring('text', 2)
sub_string(arg : string type, beginIndex : integer type, endIndex : integer type) : string type | returns a string that is a substring of this string | substring('text', 2, 3)
trim(arg: string type) : string type | returns a string, with leading and trailing whitespace omitted. | trim(str_column)
upper(arg : string type) : string type | converts all of the characters in the string to upper case | upper('abcde')

Numeric functions

Name | Description | Example
round(arg : float type | double type) : integer type | returns the argument, rounded to the nearest integer. | round(2.3478)
sgn(arg : integer type | long type | float type | double type) : appropriate type | returns the sign of the argument as `-1`, `0`, or `1`, depending on whether argument is negative, zero, or positive: | sgn (53), sgn (-47.8)
abs | sin | cos | tan (arg : integer type | long type | float type | double type) : double type | mathematical functions | abs(-5), sin(1), cos(1.2), tan (3.141)

Other functions

Name | Description | Example
current_date() : datetime type | returns the current date/time. | current_date()
nvl(arg : any type) : appropriate type | substitutes a non-value when a null value is encountered. | nvl(int_column, 0), nvl (str_column, 'unknown')

[HOME] [TOP]