Data Types

Constant Values

Customizable Value menu command can insert demo values that you may change. Also, when you select an operation, we may add some default demo values that you should edit to your own. To keep it simple, we try to fit any value in one of the following high-level data types:

1. Integer – positive or negative number with no fraction part: 0, 1234, -14…
2. Numeric – fractional number, other than integer: 0.0, 1.0, 1.2345, -34.567… They cannot be entered in other format, such as exponential or percent.
3. DateTime – date with optional time part, in #yyyy-MM-dd hh:mm:ss.ttt# or just #yyyy-MM-dd# format.
4. String – variable-size text, if it cannot be converted to any of the previous data types, or NULL. Use ‘ … ‘ enclosed notation to make sure it is considered a string: ‘Null’ will be different from Null, and ‘1’ is different from 1. When not enclosed, any ‘ within will be automatically doubled, as ”.
5. Binary – data that we only try to represent as an image, usually not available to any other operation.

One other reserved keyword and a reserved prefix:

• NULL – not a data type, but a specific “value”, which says …”no value”. It applies to any other case insensitive derived combination: Null, null, nULL etc.
• @param – anything that starts by @, in an unquoted string, is considered a parameter name, for a parameterized query

SELECT value calls to database servers may return any type of constant as a result, regardless of your database tables. However, most database platforms still require a FROM clause, even if this has nothing to do with the constant itself. We tested many databases with the following queries, to determine how they behave:

SELECT 1

SELECT 1 WHERE 1 = 1

SELECT 1 FROM DUAL GROUP BY 1 HAVING 1 = 1

Here is what we transparently use when the FROM clause is required:

1. Microsoft SQL Server, Azure and SQL CE – no FROM clause required.
2. SAP/Sybase ASE and SAP/Sybase SQL Anywhere – no FROM clause required.
3. PostgreSQL, Amazon Redshift and Ingres – no FROM clause required.
4. SQLite – no FROM clause required.
5. MySQL, MariaDB, and Amazon Aurora – no FROM clause required. But used with WHERE, it requires “FROM dual”, so we may always show this construct. Used with HAVING works in MySQL 5.7, but it fails in MySQL 5.5.
6. Oracle – required, use “FROM dual”. DUAL is a special table with one single cell always present by default in any Oracle database.
7. IBM DB2 – required, use “FROM sysibm.sysdummy1”. SYSDUMMY1 is a special table like Oracle’s DUAL, always present in any DB2 database.
8. Firebird and InterBase – required, use “FROM rdb$database”. rdb$database is a special table like Oracle’s DUAL.
9. Microsoft Access – required, but must use a real custom table, as in “FROM (SELECT COUNT(*) FROM first_table_found WHERE 1=0) AS dual”, where first_table_found is the first table we find in current specific database, which hopefully is not empty.
10. IBM Informix – required, use “FROM systables WHERE tabid=1”. SYSTABLES is a special table like Oracle’s DUAL, but to return one single row we need additional filtering.

Conversion Functions are used to either CAST a value from one data type to another, or perform some other checks and transformations:

• ToString – converts anything to a Unicode string.
• ArrayToString(separator=’|’) – converts a PostgreSQL array to elements separated by separator.
• ToInteger / ToNumber / ToLargeNumber – try to convert a string or anything else to either an integer, a small or a large float number.
• ToTrueOrFalse – converts a boolean condition to either ‘True’ or ‘False’, as a string.
• ToDateAndTime / ToDate / ToTime – try to convert a string to a date and/or time.
• IsNull – shortcut function for IS NULL operator.
• IsNotNull – shortcut function for IS NOT NULL operator.
• IfNull(expression=0) – if current value is NULL, returns the expression instead.
• NullIf(expression=0) – if current value is the expression, returns NULL instead.
• Greatest/Least(expression=0) – return either the greatest or least numeric, string or datetime value, by comparing the current context with the expression. You may also compare values returned by database fields.

Greatest and Least are not conversion functions, but they have been included here because they work with different data types. When not already exposed as built-in database functions, we emulate them through specific expressions. The Greatest and Least demo query shows how to use them:

By chaining multiple consecutive call, you create the effect of one single call with variable number of function arguments. Thus, first column is equivalent with Greatest(5, 2, 12, 3).

Numeric Data Types

Math Operations are simple arithmetical formula operations that work on any numbers. Unlike functions, in our visual SQL query builder you select an operator instead and customize its default numeric value.

• addition (+)
• substraction (-)
• multiplication (x)
• division (-)
• modulo (%) – this translates into specific database modulo operator or function.

Global Numeric Functions are top Field row functions that return special numbers:

• RandomNumber – global, generates a random number between 0 and 1.
• SequenceNumber – generates a unique sequential number for each row.
• RowNum –
• PI – returns the PI math constants, collected from the database server.

Math Functions are contextual functions that you can call on a numeric field or value:

• Round / Truncate(decimals=[2]) – rounds or truncates the fractional part to the number of decimals.
• Floor / Ceiling – gets closest lowest or highest integer.
• RightTrimZeros – removes the insignificant zeros on the right after the decimal point.
• Absolute – converts to positive number.
• Sign – returns 1 is positive, 0 for zero and -1 if negative.
• Exponent / Logarithm / Logarithm10 – exponent or logarithm (in base 10).
• SquareRoot – square root.
• Power(exponent=2) – power of, with exponent

Trigonometric Functions are geometry-related generic functions:

• SIN / ASIN / COS / ACOS / TAN / ATAN.
• ToRadians / ToDegrees – may be emulated, when not available.

Numeric Format is a client-side format string, specific and different for integers and float numbers:

String Data Type

String Operations are formula transformations that work on text fields or values:

• concatenation of text values – translates into platform-specific operations, for strings usually concatenated by the || operator.
• check if it [does not] contains, starts or ends with a string – transparently translates into a simplified LIKE clause, with no extended patterns.
• string comparison – just plain and simple alphabetic string comparison, with =, not =, <, <=, >, >= operators, including the special Is NULL and Is Not NULL.
• check if it is [not] within list/field values – translates into either IN (value1, value2, …) or IN (SELECT field FROM table). The second form uses the values returned by a database field or a subquery.

String Functions are generic contextual functions that you can apply to a text field or value:

• Length – gets number of characters in string.
• ToUpper / ToLower – converts to upper or lower cases.
• Trim / LeftTrim / RightTrim – removes starting and/or trailing spaces.
• LeftPad / RightPad(length=1, fill=[‘ ‘]) – fills one side with repeated fill string.
• Left / Right(length=1) – gets substring of length starting from one margin.
• Substring(start=1, length=1) – gets substring of length starting from a position inside.
• Position(substring=’abc’) – gets position of substring within string.
• Reverse – reverses characters in string.
• Replace(pattern=’abc’, repl=’123′) – replaces all pattern occurrences with repl.
• Insert(start=1, length=1, repl=’123′) – removes length chars from start and inserts repl.
• Repeat(times=3) – repeats current string several times.
• IsNumeric – True or False if string could be converted to a number.

String Format is a client-side format that you can use for the grid values. It is however recommended to use equivalent server-side functions for similar effects, when available:

Date/Time Data Type

Get Current Date and Time with a call to one of the following global functions, in the top Field row:

• DatabaseDateAndTime / DatabaseDate / DatabaseTime – gets the date and/or time exposed remotely by your database server.
• MyDateAndTime / MyDate / MyTime – gets the date and/or time exposed locally by your computer.
• UniversalDateAndTime / UniversalDate / UniversalTime – gets the GMT date and/or time.

Extract from DateTime contextual functions extract some granular specific information from a DateTime field or value:

• ToMonthName – January, February, …
• ToWeekDayName – Monday, Tuesday, …
• ToYears / ToQuarters / ToMonths / ToWeeks – 2016 / 1..4 / 1..12 / 1..52.
• ToDaysOfYear / ToDaysOfMonth / ToDaysOfWeek – 1..366 / 1..31 / 1..7.
• ToHours / ToMinutes / ToSeconds / ToMilliseconds – 0..24 / 0..60 / 0..60 / 0..999.

Add to DateTime functions can add some granular information (with default value 1, that you can change) to a DateTime value used as current context:

• AddYears(years=1) / AddMonths(months=1)
• AddWeeks(weeks=1) / AddDays(days=1)
• AddHours(hours=1) / AddMinutes(minutes=1)
• AddSeconds(seconds=1) / AddMilliseconds(milliseconds=1)

DateTime Difference functions subtract some granular information from a DateTime value:

• DiffInYears (…) / DiffInMonths(…)
• DiffInWeeks(…) / DiffInDays(…)
• DiffInHours(…) / DiffInMinutes(…)
• DiffInSeconds(…) / DiffInMilliseconds(…)

DateTime Format is a client-side format you may use for your DateTime values in the result grid:

Following query gets current date and time from your specific database server, appends 2 more years and 10 hours to it, and displays it in a specific client-side format:

While in the visual SQL builder you use the same syntax and conventions for ANY connected database, the generated query is always specific to your platform. Here is what we may generate for MySQL v8:

SELECT TIMESTAMPADD(HOUR, 10, TIMESTAMPADD(YEAR, 2, NOW())) AS `value`