|
|
The features on this page require a GENESIS64 Advanced license and are not available with GENESIS64 Basic SCADA . |
|
|
The features on this page require a GENESIS64 Advanced license and are not available with GENESIS64 Basic SCADA . |
AnalytiX-BI supports a large number of built-in functions for arithmetic, string manipulation, date/time and more. Most of the functions supported by the ICONICS Expression Editor are also supported in AnalytiX-BI with the same name. All function names are case-insensitive.
For most functions, when a parameter is NULL, then the result of the function will also be NULL:
SELECT 1 + NULL --returns NULL
SELECT sin(NULL) --returns NULL
Evaluates the arguments in order and returns the current value of the first expression that initially doesn’t evaluate to NULL. The resulting data type of the COALESCE function is the highest data type of its arguments.
The following query returns a comma-separated country and region for each customer, or just the country when the region is NULL.
SELECT COALESCE(Country + ', ' + Region, Country) FROM Customers
Evaluates the first argument and, if it evaluates to NULL, returns the value of the second argument; otherwise returns the first argument.
The following query returns the region for each customer, and the string “no region” when the region is NULL.
SELECT ISNULL(Region, 'No Region') FROM Customers
Basic arithmetic operators, rounding functions, trigonometric functions, etc.
Calculates the absolute value of the input expression.
SELECT abs(ProductID - 35) FROM Products
Calculates the arccosine of the input expression.
SELECT acos(-1) --returns π
Calculates the arcsine of the input expression.
SELECT asin(1) --returns π/2
Calculates the arctangent of the input expression.
SELECT atan(1) --returns π/4
Returns the smallest integral value that is greater than or equal to the specified input expression. The result type is either Double or Decimal, depending on the input.
SELECT ceil(0.1) --returns 1
SELECT ceiling(0.9) --returns 1
Calculates the cosine of the input expression.
SELECT cos(2 * pi()) --returns 1
Calculates the cotangent of the input expression.
SELECT cot(pi() /4) --returns 1
Returns the natural logarithmic base, specified by the constant e.
SELECT e() --returns 2.718…
Returns e raised to the specified power.
SELECT exp(2) --returns e2
Returns the largest integral value that is less than or equal to the specified input expression. The result type is either Double or Decimal, depending on the input.
SELECT floor(1.1) --returns 1
SELECT floor(1.9) --returns 1
Returns the maximum from the specified list of input expressions. This function supports an unlimited number of parameters. The following query retrieves, for every row, the maximum value among all columns listed and the constant 30.
SELECT greatest(UnitsInStock, UnitsOnOrder, ReorderLevel, 30) FROM Products
Returns a Boolean indicating whether the specified expression evaluates to negative or positive infinity.
SELECT isinfinity(cot(0)) --returns true
SELECT isinfinity(1) --returns false
Returns a Boolean indicating whether the specified value is not a number. Floating-point operations return NaN to signal that the result of the operation is not defined.
SELECT isnan(0 / 0.0) --returns true
Returns the minimum from the specified list of input expressions. This function supports an unlimited number of parameters. The following query retrieves, for every row, the minimum value among all columns listed and the constant 10.
SELECT least(UnitsInStock, ReorderLevel, 10) FROM Products WHERE ReorderLevel > 0
Returns the natural (base e) logarithm of the specified expression.
SELECT ln(e()) --returns 1
Returns the base 10 logarithm of the specified expression.
SELECT log(10) --returns 1
Returns the constant π
SELECT pi() --returns π
Raises a number to the specified power.
SELECT pow(2, 3) --returns 8
SELECT power(3, 2) --returns 9
Rounds the input expression to the nearest integral value, and rounds midpoint values to the nearest even number.
SELECT round(4.4) --returns 4
SELECT round(4.5) --returns 4
SELECT round(4.6) --returns 5
SELECT round(5.5) --returns 6
Rounds the input expression to a specified number of fractional digits, and rounds midpoint values to the nearest even number.
SELECT roundto(3.44, 1) --returns 3.4
SELECT roundto(3.45, 1) --returns 3.4
SELECT roundto(3.46, 1) --returns 3.5
Returns an integer that indicates the sign of the specified input expression.
SELECT sign(2) --returns 1
SELECT sign(-2) --returns -1
Calculates the sine of the input expression.
SELECT sin(pi() / 2) --returns 1
Calculates the square root of the input expression.
SELECT sqrt(9) --returns 3
Calculates the square of the input expression.
SELECT square(5) --returns 25
Calculates the tangent of the input expression.
SELECT tan(pi() / 4) --returns 1
Bit-testing and bit-manipulation functions.
Tests if the bit at the specified positi254on in the specified numeric expression is equal to 1. The syntax is:
bittest(numeric_expression_to_test, index_of_the_bit_to_test)
SELECT bittest(2, 0) --returns false
SELECT bittest(2, 1) --returns true
Sets the bit at the specified position in the specified numeric expression to the specified value. The syntax is:
setbit(numeric_expression, index_of_the_bit_to_set, value_of_the_bit)
SELECT setbit(2, 0, 1) --returns 3
SELECT setbit(2, 1, 0) --returns 0
Shifts the bits of the specified numeric expression to the left by the specified number of positions. The syntax is:
shl(numeric_expression, number_of_positions_by_which_to_shift_bits)
SELECT shl(1, 3) --returns 8
Shifts the bits of the specified numeric expression to the right by the specified number of positions. The syntax is:
shr(numeric_expression, number_of_positions_by_which_to_shift_bits)
SELECT shr(8, 3) --returns 1
Toggles the bit of the specified numeric expression at the specified index. The syntax is:
togglebit(numeric_expression, index_of_the_bit_to_toggle)
SELECT togglebit(2, 0) --returns 3
SELECT togglebit(2, 1) --returns 0
These functions provide type-conversion and formatting functionality.
Converts an expression of one data type to another. The syntax is:
cast(expression AS data_type)
For the list of data types supported by AnalytiX-BI, refer to the data types section. The following query casts a double value to integer.
SELECT cast(1.232 AS Int32) --returns 1
When converting strings to another type, the conversion is performed using the culture from the data model’s collation. For more information about the data model’s collation, please refer to the Culture and Collation section. Assuming that the data model’s culture is en-us, the following query convert the string 1.143 to a Double.
SELECT cast('1.143' AS Double) --returns 1.143
It is possible to override the data model’s collation used by the conversion by using the COLLATE keyword. The following query converts the string 1,143 from the it-it culture (which uses the comma as decimal separator) to a Double.
SELECT cast('1,143' COLLATE it-it_ci AS Double) --returns 1.143
Conversion from string to DateTime or DateTimeOffset can accept the input in different formats. As previously explained, the culture used for the conversion is the culture of the data model’s collation and it is possible to override the culture by using the COLLATE keyword.
The following queries convert a date-only string conforming to ISO 8601 and a date/time string using en-us culture to DateTime values.
SELECT cast('2021-09-28' AS DateTime)
SELECT cast('09/28/2021 1:41 PM' AS DateTime)
The following query converts an ISO 8601 date/time string with offset information to DateTimeOffset.
SELECT cast('2021-09-28T17:43:12.123-05' AS DateTimeOffset)
For more information on input strings, please refer to .NET’s DateTime.Parse documentation.
When converting a string to a TimeSpan, the string must have the following format:
[-]{ d | [d.]hh:mm[:ss[.ff]] }
Elements in square brackets are optional; the vertical bar (|) represents a choice between the left or the right option. The milliseconds separator is culture-specific, and the culture used for the conversion is the culture of the data model’s collation.
The following queries show a few examples of conversion to TimeSpan.
SELECT cast('6' AS TimeSpan) --returns 6 days
SELECT cast('6:12' AS TimeSpan) --returns 6 hours, 12 minutes
SELECT cast('6:12:14' AS TimeSpan) --returns 6 hours, 12 minutes, 14 seconds
SELECT cast('1.6:12:14' AS TimeSpan) --returns 1 day, 6 hours, 12 minutes, 14 seconds
SELECT cast('6:12:14.123' AS TimeSpan) --returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds
SELECT cast('6:12:14,123' COLLATE it-it_ci AS TimeSpan) --returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds
Input strings supported for conversion to Boolean are either “true” or “false” (case-insensitive). The following queries show conversion from string to Boolean.
SELECT cast('true' AS Boolean) --returns true
SELECT cast('false' AS Boolean) --returns false
When converting other types to String the data model’s collation is used to format the output string. The following query converts the double 1.143 to String when the data model’s culture is en-us.
SELECT cast(1.143 AS String) --returns "1.143"
Note: Double or Single constants must always be specified using period as a decimal separator in AnalytiX-BI.
It is not possible to specify a custom culture for the conversion, however the format of the converted string can be customized using the CONVERT function.
When converting a number to DateTime, the value will be interpreted as number of days elapsed since January 1, 1900 at 12:00 AM. If the value has a fractional part, it will be interpreted as the fractional part of a day. For example, 4.5 is equivalent to 4 days, 12 hours, 0 minutes, 0 seconds, 0 milliseconds.
The following queries show a few examples of conversion to DateTime.
SELECT cast(0 AS DateTime) --returns January 1, 1900, 12:00 AM
SELECT cast(1 AS DateTime) --returns January 2, 1900, 12:00 AM
SELECT cast(2.25 AS DateTime) --returns January 3, 1900, 6:00 AM
The conversion takes into account leap years and the number of days in a month when performing the date arithmetic.
Converting DateTime values to numbers assumes that January 1, 1900 at 12:00 AM is the zero value, as shown in the following examples.
SELECT cast(cast('1900-01-01' AS DateTime) AS Int32) --returns 0
SELECT cast(cast('1899-12-31' AS DateTime) AS Int32) --returns -1
SELECT cast(cast('1900-01-02' AS DateTime) AS Int32) --returns 1
When converting a number to TimeSpan, the value will be interpreted as the number of milliseconds. If the value has a fractional part, it will be interpreted as fraction of a millisecond. For example, 1.5 is equivalent to 1 millisecond and 5 thousandths of a second.
The following queries show a few examples to conversion to TimeSpan.
SELECT cast(100 AS TimeSpan) --returns 00:00:00.1000
SELECT cast(100.1 AS TimeSpan) --returns 00:00:00.1001
SELECT cast(1000 AS TimeSpan) --returns 00:00:01.0000
Converts an expression of one data type to another. CHANGETYPE and TOTYPE can be used interchangeably. The syntax is:
totype(expression, 'data_type_as_string')
The string representing the data type must be a constant (no column references or expressions allowed) from one of the following case-insensitive values:
· string
· bool or boolean
· byte
· sbyte
· int16 or short
· uint16 or ushort
· int32 or int
· uint32 or uint
· int64 or long
· uint64 or ulong
· single or float
· double
· decimal
· timespan
· datetime
The following query casts a double value to integer.
SELECT totype(1.234, 'int') --returns 1
Note: these functions are implemented for compatibility with the Expression Engine functions with the same name. In AnalytiX-BI queries use of the CAST function is recommended.
Converts an expression of one data type to another. The syntax is:
convert(expression AS data_type, 'format_string')
The CONVERT function provides the exact same functionality as the CAST function, with the difference that when converting to String, the format_string parameter can be used to customize the string output. Passing an empty format string makes CONVERT behave as CAST when converting to String. For conversion to all other data types, the format_string parameter is ignored.
All conversions are performed using the culture of the data model’s collation, and it is not possible to specify a custom culture for the conversion.
The following query formats the UnitPrice of products with the currency format. The currency symbol used depends on the data model’s collation.
SELECT convert(UnitPrice AS String, 'c') FROM Products
The following query formats the ProductID of products by adding leading zeros so that the result has always 4 digits.
SELECT convert(ProductID AS String, 'D4') FROM Products
For more information on format strings, please refer to the .NET documentation:
· Standard numeric format strings
· Custom numeric format strings
· Standard date and time format strings
· Custom date and time format strings
· Standard TimeSpan format strings
· Custom TimeSpan format strings
Converts an expression to Boolean. If the source expression is a string, it is always interpreted using the invariant culture, even if the COLLATE keyword is used. The following queries show a few examples of the TOBOOLEAN function.
SELECT toboolean(0) --returns false
SELECT toboolean('true') --returns true
SELECT toboolean('1.1') --returns true in any culture
SELECT toboolean('1,1') --fails because the conversion uses the invariant culture
SELECT toboolean('1,1' COLLATE it-it_ci) --also fails because the conversion always uses the invariant culture, and COLLATE does not override it
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.
Converts an expression to Boolean. If the source expression is a string, it is interpreted using the culture of the data model’s collation. The culture used for the conversion can be overridden by applying the COLLATE keyword. The following queries show a few examples of the TOBOOLEANCULTURE function.
SELECT tobooleanculture(0) --returns false
SELECT tobooleanculture('true') --returns true
SELECT tobooleanculture('1.1') --returns true in the en-us culture
SELECT tobooleanculture('1,1') --fails in the en-us culture
SELECT tobooleanculture('1,1' COLLATE it-it_ci) --returns true
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.
Converts an expression to DateTime. If the source expression is a string, it is always interpreted using the invariant culture, even if the COLLATE keyword is used. The following queries show a few examples of the TODATETIME function.
SELECT todatetime(0) --returns January 1, 1900 12:00 AM
Note: the Expression Engine does not allow converting numbers to DateTime, however since the internal implementation of TODATETIME in AnalytiX-BI uses the CAST function, this case is supported.
SELECT todatetime('2021-09-29') --returns September 29, 2021 12:00 AM
SELECT todatetime('09/29/2021') --returns September 29, 2021 12:00 AM
SELECT todatetime('29/09/2021') --fails because the conversion uses the invariant culture
SELECT todatetime('29/09/2021' COLLATE it-it_ci) --also fails because the conversion always uses the invariant culture, and COLLATE does not override it
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.
Converts an expression to DateTime. If the source expression is a string, it is interpreted using the culture of the data model’s collation. The culture used for the conversion can be overridden by applying the COLLATE keyword. The following queries show a few examples of the TODATETIMECULTURE function.
SELECT todatetimeculture(0) --returns January 1, 1900 12:00 AM
Note: the Expression Engine does not allow converting numbers to DateTime, however since the internal implementation of TODATETIMECULTURE in AnalytiX-BI uses the CAST function, this case is supported.
SELECT todatetimeculture('2021-09-29') --returns September 29, 2021 12:00 AM
SELECT todatetimeculture('09/29/2021') --returns September 29, 2021 12:00 AM in the en-us culture
SELECT todatetimeculture('29/09/2021') --fails in the en-us culture
SELECT todatetimeculture('29/09/2021' COLLATE it-it_ci) --returns September 29, 2021 12:00 AM
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.
Converts an expression to String using the provided format string. The culture used for the conversion is always the invariant culture, and it is not possible to specify a custom collation for the conversion. The following queries show a few examples of the TOFORMAT function.
SELECT toformat(UnitPrice, 'c') FROM Products --returns prices with the invariant culture currency symbol ¤
SELECT toformat(ProductID, 'D4') FROM Products --returns product IDs as 4 digits with leading zeroes
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CONVERT function is recommended. Please also refer to the CONVERT function for more information on format strings.
Converts an expression to String using the provided format string. The culture used for the conversion is the culture of the data model’s collation, and it is not possible to specify a custom collation for the conversion. The following queries show a few examples of the TOFORMATCULTURE function.
SELECT toformatculture(UnitPrice, 'c') FROM Products --returns prices with the currency symbol $ in en-us culture
SELECT toformatculture(ProductID, 'D4') FROM Products --returns product IDs as 4 digits with leading zeroes
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CONVERT function is recommended. Please also refer to the CONVERT function for more information on format strings.
Converts an expression to Double. If the source expression is a string, it is always interpreted using the invariant culture, even if the COLLATE keyword is used. The following queries show a few examples of the TONUMBER function.
SELECT tonumber('1') --returns 1 (double)
Note: the Expression Engine returns an Int64 for strings that represent integers, whereas AnalytiX-BI always returns Double.
SELECT tonumber('1.1') --returns 1.1 in any culture
SELECT tonumber('1,1') --fails because the conversion uses the invariant culture
SELECT tonumber('1,1' COLLATE it-it_ci) --also fails because the conversion always uses the invariant culture, and COLLATE does not override it
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.
Converts a string, which represents an integral number in the specified base, to Int64. The conversion does not use culture information for the conversion. The following queries show a few examples of the TONUMBERBASE function.
SELECT tonumberbase('11001', 2) --returns 25 (from binary 11001)
SELECT tonumberbase('10', 8) --returns 8 (from octal 10)
SELECT tonumberbase('18', 10) --returns 18 (from decimal 18)
SELECT tonumberbase('18.1', 10) --fails
Note: the Expression Engine can convert base-10 fractional values to Double, however this is not supported in AnalytiX-BI.
SELECT tonumberbase('1a', 16) --returns 26 (from hexadecimal 1a)
Converts an expression to Double. If the source expression is a string, it is interpreted using the culture of the data model’s collation. The culture used for the conversion can be overridden by applying the COLLATE keyword. The following queries show a few examples of the TONUMBERCULTURE function.
SELECT tonumberculture('1') --returns 1 (double)
Note: the Expression Engine returns an Int64 for strings that represent integers, whereas AnalytiX-BI always returns Double.
SELECT tonumberculture('1.1') --returns 1.1 in the en-us culture
SELECT tonumberculture('1,1') --fails in the en-us culture
SELECT tonumberculture('1,1' COLLATE it-it_ci) --returns 1.1
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.
Converts an expression to String. The culture used for the conversion is always the invariant culture, and it is not possible to specify a custom collation for the conversion. The following queries show a few examples of the TOSTRING function.
SELECT tostring(false) --returns 'False'
SELECT tostring(1.234) --returns '1.234'
SELECT tostring(now()) --returns the current date/time formatted with the invariant culture
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.
Converts an expression to String. The culture used for the conversion is the culture of the data model’s collation, and it is not possible to specify a custom collation for the conversion. The following queries show a few examples of the TOSTRINGCULTURE function.
SELECT tostring(false) --returns 'False'
SELECT tostring(1.234) --returns '1.234' in a en-us culture
SELECT tostring(now()) --returns the current date/time formatted with the data model’s culture
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.
Converts an expression to TimeSpan. If the source expression is a string, it is always interpreted using the invariant culture, even if the COLLATE keyword is used. The following queries show a few examples of the TOTIMESPAN function.
SELECT totimespan(100) --returns 00:00:00.1000
SELECT totimespan(100.1) --returns 00:00:00.1001
SELECT totimespan(1000) --returns 00:00:01.0000
SELECT totimespan('6:12:14.123') --returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds
SELECT totimespan('6:12:14,123') --fails because the conversion uses the invariant culture
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.
Converts an expression to TimeSpan. If the source expression is a string, it is interpreted using the culture of the data model’s collation. The culture used for the conversion can be overridden by applying the COLLATE keyword. The following queries show a few examples of the TOTIMESPANCULTURE function.
SELECT totimespanculture(100) --returns 00:00:00.1000
SELECT totimespanculture(100.1) --returns 00:00:00.1001
SELECT totimespanculture(1000) --returns 00:00:01.0000
SELECT totimespanculture('6:12:14.123') --returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds
SELECT totimespanculture('6:12:14,123' COLLATE it-it_ci) --returns 6 hours, 12 minutes, 14 seconds, 123 milliseconds
Note: this function is implemented for compatibility with the Expression Engine function with the same name. In AnalytiX-BI queries use of the CAST function is recommended.
These functions operate on DateTime values and provide related functionality.
Adds the specified number of days to a DateTime expression. The number of days can be negative, which will subtract the value from the DateTime expression.
SELECT adddays(cast('2021-09-29 18:32:45.123' AS DateTime), 1) --returns September 30, 2021 6:32:45.123 PM
Adds the specified number of months to a DateTime expression. The number of months can be negative, which will subtract the value from the DateTime expression.
SELECT addmonths(cast('2021-09-29 18:32:45.123' AS DateTime), 1) --returns October 29, 2021 6:32:45.123 PM
Adds the specified number of years to a DateTime expression. The number of years can be negative, which will subtract the value from the DateTime expression.
SELECT addyears(cast('2021-09-29 18:32:45.123' AS DateTime), 1) --returns September 30, 2022 6:32:45.123 PM
Returns a DateTime value where the time part of the specified DateTime expression is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.
SELECT bday(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns September 29, 2021 12:00:00.000 AM
Returns a DateTime value where the time part of the specified DateTime expression is set to the beginning of the hour. The returned DateTime value has its Kind property set to Unspecified.
SELECT bhour(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns September 29, 2021 6:00:00.000 PM
Returns a DateTime value where the time part of the specified DateTime expression is set to the beginning of the minute. The returned DateTime value has its Kind property set to Unspecified.
SELECT bminute(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns September 29, 2021 6:32:00.000 PM
Returns a DateTime value where the day is set to the first day of the month relative to the specified DateTime expression and the time part is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.
SELECT bmonth(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns September 1, 2021 12:00:00.000 AM
Returns a DateTime value where the time part of the specified DateTime expression is set to the beginning of the second. The returned DateTime value has its Kind property set to Unspecified.
SELECT bsecond(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns September 29, 2021 6:32:45.000 PM
Returns a DateTime value where the day is set to the first day of the week relative to the specified DateTime expression and the time part is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.
The first day of the week is determined by the culture associated to the data model’s collation.
SELECT bweek(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns September 26, 2021 12:00:00.000 AM (Sunday) for the en-us culture
SELECT bweek(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns September 27, 2021 12:00:00.000 AM (Monday) for the it-it culture
Returns a DateTime value where the day is set to the first day of the year relative to the specified DateTime expression and the time part is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.
SELECT byear(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns January 1, 2021 12:00:00.000 AM
Returns the specified part from the specified DateTime expression. The syntax is:
datepart(datetime_expression, 'date_part')
The date_part parameter can be one of the following values (case-insensitive):
· year
· month
· day
· dayofweek
· dayofweekoffset
· hour
· minute
· second
· millisecond
The following queries show examples with the DATEPART function.
SELECT datepart(cast('2021-09-29 18:32:45.123' AS DateTime), 'year') --returns 2021
SELECT datepart(cast('2021-09-29 18:32:45.123' AS DateTime), 'month') --returns 9
SELECT datepart(cast('2021-09-29 18:32:45.123' AS DateTime), 'day') --returns 29
SELECT datepart(cast('2021-09-29 18:32:45.123' AS DateTime), 'dayofweek') --returns 3 (Wednesday); dayofweek is culture-invariant and returns a value between 0 (Sunday) to 6 (Saturday)
SELECT datepart(cast('2021-09-29 18:32:45.123' AS DateTime), 'dayofweekoffset') --returns a value relative to the first day of week in the culture of the data model’s collation
Note: see the DAYOFWEEKOFFSET function for more details
SELECT datepart(cast('2021-09-29 18:32:45.123' AS DateTime), 'hour') --returns 18
SELECT datepart(cast('2021-09-29 18:32:45.123' AS DateTime), 'minute') --returns 32
SELECT datepart(cast('2021-09-29 18:32:45.123' AS DateTime), 'second') --returns 45
SELECT datepart(cast('2021-09-29 18:32:45.123' AS DateTime), 'millisecond') --returns 123
Builds a DateTime value from individual parts, the returned DateTime value has its Kind property set to Unspecified. The function has three different overloads:
datetimefromparts(year_expression, month_expression, day_expression, hour_expression, minute_expression, second_expression, millisecond_expression)
datetimefromparts(year_expression, month_expression, day_expression, hour_expression, minute_expression, second_expression)
datetimefromparts(year_expression, month_expression, day_expression)
The following query shows how to use the DATETIMEFROMPARTS function to build a DateTime value.
SELECT datetimefromparts(2021, 09, 29, 18, 32, 45, 123) --returns September 29, 2021 6:32:45.123 PM
Returns the day part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is day.
SELECT day(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 29
Returns the name of the day of a DateTime expression in the invariant culture. Invariant culture day names are the English Sunday, Monday, Tuesday, Wednesday, Thursday, Friday and Saturday.
SELECT dayname(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 'Wednesday'
Returns the name of the day of a DateTime expression in the culture of the data model’s collation.
SELECT daynameculture(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 'Wednesday' in en-us culture
SELECT daynameculture(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 'mercoledì' in it-it culture
Returns the numeric index of the day of a DateTime expression in the invariant culture. This function is a shortcut for DATEPART when the date_part parameter is dayofweek.
In the invariant culture the first day of the week is always Sunday with index 0, and the last day of the week is Saturday with index 6.
SELECT dayofweek(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 3 (Wednesday)
Returns the numeric index of the day of a DateTime expression in the culture of the data model’s collation. This function is a shortcut for DATEPART when the date_part parameter is dayofweekoffset.
The following query returns 3 as day of the week when it runs in the en-us culture, where the first day of the week is Sunday.
SELECT dayofweekoffset(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 3 (Wednesday)
The same query returns 2 as day of the week when it runs in the it-it culture, where the first day of the week is Monday.
SELECT dayofweekoffset(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 2 (Wednesday)
Returns the number of seconds elapsed since the beginning of the day of a DateTime expression.
SELECT dayseconds(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 66765
Returns a DateTime value where the day is set to the last day of the month relative to the specified DateTime expression and the time part is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.
SELECT emonth(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns September 30, 2021 12:00:00.000 AM
Returns a DateTime value where the day is set to the last day of the week relative to the specified DateTime expression and the time part is set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.
The last day of the week is determined by the culture associated to the data model’s collation.
SELECT eweek(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns October 2, 2021 12:00:00.000 AM (Saturday) for the en-us culture
SELECT eweek(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns October 3, 2021 12:00:00.000 AM (Sunday) for the it-it culture
Returns a DateTime value where the day is set to the last day of the year relative to the specified DateTime expression and the time part is set to 12:00:00 AM.
SELECT eyear(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns December 31, 2021 12:00:00.000 AM
Converts a Unix seconds epoch to a DateTime value. The returned DateTime value is in UTC time and has its Kind property set to Utc.
SELECT fromepoch(1632940365) --returns September 29, 2021 6:32:45 PM
Converts a Unix seconds epoch to a DateTimeOffset value.
SELECT fromepochoffset(1632940365) --returns September 29, 2021 6:32:45 PM GMT
This function has two overloads:
getdate()
Returns a DateTime value representing the current date and time. The result of this function is the same as the NOW function. The returned DateTime value has its Kind property set to Unspecified.
SELECT getdate() --returns the current date and time
The second overload accepts a DateTime expression as parameter, and returns the date portion with the time set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.
SELECT getdate(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns September 29, 2021 12:00:00.000 AM
Returns the time portion of a DateTime expression as a TimeSpan.
SELECT gettimeofday(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 0.18:32:45.123
Returns a DateTime value representing the current date and time. The result of this function is the same as the UTCNOW function. The returned DateTime value has its Kind property set to Utc.
SELECT getutcdate() --returns the current date and time in UTC
Returns the hour part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is hour.
SELECT hour(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 18
Returns whether a DateTime expression represents a point in time within a Daylight Savings Time period or not. If the Kind property of the DateTime expression is Unspecified or Local, the value will be considered a local time – where “local” is the time zone configured for the machine. If the Kind property is Utc, the ISDST function will return false.
SELECT isdst(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns true for Eastern Time (US & Canada)
SELECT isdst(cast('2021-02-20 18:32:45.123' AS DateTime)) --returns false for Eastern Time (US & Canada)
SELECT isdst(utcnow()) --always returns false
Returns the maximum value for DateTime expressions.
SELECT maxtime() -- returns 12/31/9999 11:59:59 PM
Returns the millisecond part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is millisecond.
SELECT millisecond(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 123
Returns the minimum value for DateTime expressions. Note that DateTime expressions are allowed to assume a value lesser than MINTIME, however MINTIME produces the exact DateTime value that, when converted to a numeric type, returns zero.
SELECT cast(mintime() AS Int32) --returns 0
Returns the minute part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is minute.
SELECT minute(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 32
Returns the month part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is month.
SELECT month(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 9
Returns the name of the month of a DateTime expression in the invariant culture. Invariant culture day names are the English January, February, March, April, May, June, July, August, September, October, November and December.
SELECT monthname(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 'September'
Returns the name of the month of a DateTime expression in the culture of the data model’s collation.
SELECT monthnameculture(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 'September' in en-us culture
SELECT monthnameculture(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 'settembre' in it-it culture
Returns the date portion with the time set to 12:00:00 PM. The returned DateTime value has its Kind property set to Unspecified.
SELECT noon(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns September 29, 2021 12:00:00.000 PM
Returns the current date and time. The returned DateTime value has its Kind property set to Unspecified.
SELECT now() --returns the current date and time
Returns the second part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is second.
SELECT second(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 45
Returns the current date with the time set to 12:00:00 AM. The returned DateTime value has its Kind property set to Unspecified.
SELECT today() --returns the current date with the time portion set to 12:00:00 AM
Converts a DateTime expression into a Unix epoch in seconds. If the DateTime expression has its Kind property set to Unspecified it will be considered as local time, and converted to UTC before getting converted to epoch.
SELECT toepoch(cast('2021-09-29T18:32:45Z' AS DateTime)) --CAST returns a DateTime with Kind set to Utc, so returns 1632940365
SELECT toepoch(cast('2021-09-29T18:32:45' AS DateTime)) --CAST returns a DateTime with Kind set to Unspecified, which is interpreted as local time, so returns 1632954765 (representing September 29, 2021 14:32:45 UTC)
Note: the behavior of this function is different from the equivalent function in the Expression Engine: in the Expression Engine, the DateTime’s Kind property is ignored and the value always assumed to be UTC.
Converts a DateTime expression to local time and returns a DateTime value with its Kind property set to Local.
The result of the conversion is dependent on the Kind property of the expression.
· Utc. The expression is converted to local time.
· Local. No conversion is performed.
· Unspecified. The expression is assumed to be UTC and the conversion is performed as if Kind were Utc.
select tolocal(cast('2021-09-29T18:32:45Z' AS DateTime)) --returns September 29, 2021 18:32:45 UTC in local time
Converts a DateTime expression from one time zone to another. The function has two overloads:
totimezonetime(datetime_expression, destination_time_zone)
totimezonetime(datetime_expression, destination_time_zone, source_time_zone)
When used without the source_time_zone parameter, the datetime_expression’s Kind property determines the source time zone. Both source_time_zone and destination_time_zone are case-insensitive strings, and can be either a time zone identifier or a fixed time offset.
To retrieve the identifier for a specific time zone, it is possible to use the Time Zone picker for Equipment in the Assets provider in Workbench.
When the input DateTime expression’s Kind property is set to Local or Unspecified, it will be considered local time.
SELECT totimezonetime('2021-09-29T12:00:00', 'Eastern Standard Time') --returns September 29, 2021 12:00:00 PM when run in Eastern Time (US & Canada)
SELECT totimezonetime('2021-09-29T12:00:00', 'Pacific Standard Time') --returns September 29, 2021 9:00:00 AM when run in Eastern Time (US & Canada)
When the input DateTime expression’s Kind property is set to Utc, it will be considered UTC time.
SELECT totimezonetime('2021-09-29T12:00:00Z', 'Eastern Standard Time') --returns September 29, 2021 8:00:00 AM in any time zone
When the input DateTime expression’s Kind property is set to Utc the source_time_zone parameter will be ignored even if specified. The following query returns September 29, 2021 8:00:00 AM even if the source time zone is set to Eastern Standard Time because the input is a UTC DateTime.
SELECT totimezonetime('2021-09-29T12:00:00Z', 'Eastern Standard Time', 'Eastern Standard Time') --returns September 29, 2021 8:00:00 AM in any time zone
When the input DateTime expression’s Kind property is set to Local or Unspecified, it will be interpreted as a local time in the source time zone. The following query translates a DateTime value from Pacific Standard Time to Eastern Standard Time.
SELECT totimezonetime('2021-09-29T12:00:00', 'Eastern Standard Time', 'Pacific Standard Time') --returns September 29, 2021 3:00:00 PM
Conversions take into account daylight savings status. The following query shows a 6 hours difference between Eastern Time and Central European Time when daylight savings was still in effect in both time zones.
SELECT totimezonetime('2020-10-24T12:00:00', 'Central Europe Standard Time', 'Eastern Standard Time') --returns October 24, 2020 6:00:00 PM (6 hours difference)
The following query shows a 5 hours difference because daylight savings ended in Central European Time, but not in Eastern Time.
SELECT totimezonetime('2020-10-25T12:00:00', 'Central Europe Standard Time', 'Eastern Standard Time') --returns October 25, 2020 5:00:00 PM (5 hours difference)
Converts a DateTime expression to Universal Coordinated Time and returns a DateTime value with its Kind property set to Utc.
The result of the conversion is dependent on the Kind property of the expression.
· Utc. No conversion is performed.
· Local. The expression is converted to UTC.
· Unspecified. The expression is assumed to be local time and the conversion is performed as if Kind were Local.
select toutc(cast('2021-09-29T18:32:45' AS DateTime)) --returns September 29, 2021 18:32:45 local time in UTC
Returns the current UTC date and time. The returned DateTime value has its Kind property set to Utc.
SELECT utcnow() --returns the current UTC date and time
Returns the numeric index of the day of a DateTime expression in the invariant culture. In the invariant culture the first day of the week is always Sunday with index 0, and the last day of the week is Saturday with index 6.
SELECT weekday(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 3 (Wednesday)
This function returns the same result as DAYOFWEEK.
Returns a number between 1 and 53 representing the week in the year of the given date in the Gregorian calendar. The function has three overloads:
weeknumber(datetime_expression)
weeknumber(datetime_expression, week_rule)
weeknumber(datetime_expression, week_rule, firstDayofWeek)
The week_rule parameter is an enumerated integer that can assume the values described in the following list. The default value is 0 (FirstDay).
· 0 (FirstDay). Indicates that the first week of the year starts on the first day of the year and ends before the following designated first day of the week.
· 1 (FirstFullWeek). Indicates that the first week of the year begins on the first occurrence of the designated first day of the week.
· 2 (FirstFourDayWeek). Indicates that the first week of the year is the first week with four or more days before the designated first day of the week.
The firstDayofWeek parameter indicates what the first day in the week is. The default value is 0 (Sunday).
SELECT weeknumber(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 40
SELECT weeknumber(cast('2021-09-29 18:32:45.123' AS DateTime), 1) --returns 39
SELECT weeknumber(cast('2021-09-29 18:32:45.123' AS DateTime), 1, 4) --returns 38
SELECT weeknumber(cast('2021-09-29 18:32:45.123' AS DateTime), 2) --returns 39
Returns a number between 1 and 53 representing the week in the year of the given date in the default calendar in the culture of the data model’s collation. The function has three overloads:
weeknumberculture(datetime_expression)
weeknumberculture(datetime_expression, week_rule)
weeknumberculture(datetime_expression, week_rule, firstDayofWeek)
The week_rule parameter is an enumerated integer that can assume the values described in the following list. The default value is 0 (FirstDay).
· 0 (FirstDay). Indicates that the first week of the year starts on the first day of the year and ends before the following designated first day of the week.
· 1 (FirstFullWeek). Indicates that the first week of the year begins on the first occurrence of the designated first day of the week.
· 2 (FirstFourDayWeek). Indicates that the first week of the year is the first week with four or more days before the designated first day of the week.
The firstDayofWeek parameter indicates what the first day in the week is. The default value is 0 (Sunday).
SELECT weeknumberculture(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 40
SELECT weeknumberculture(cast('2021-09-29 18:32:45.123' AS DateTime), 1) --returns 39
SELECT weeknumberculture(cast('2021-09-29 18:32:45.123' AS DateTime), 1, 4) --returns 38
SELECT weeknumberculture(cast('2021-09-29 18:32:45.123' AS DateTime), 2) --returns 39
Returns the date of the day before the current date, with its time part set to 12:00:00 AM. The returned value has its Kind property set to Unspecified.
SELECT yday() --returns yesterday's date at 12:00:00 AM
Returns the year part of a DateTime expression. This function is a shortcut for DATEPART when the date_part parameter is year.
SELECT year(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 2021
Returns the day of the year of a DateTime expression. The result is a number between 1 and 366.
SELECT yearday(cast('2021-09-29 18:32:45.123' AS DateTime)) --returns 272
These functions operate on Unit32 Status Codes, typically returned by an operation on real-time or historical values.
Returns a human-readable string of the UInt32 expression representing a Status Code.
SELECT getdescq(2147483658) --returns "Bad - User Access Denied"
Determines if the specified UInt32 expression represents a Bad Status Code.
SELECT isbadq(0) --returns false
SELECT isbadq(134217728) --returns true
Determines if the specified UInt32 expression represents a Good Status Code.
SELECT isgoodq(0) --returns true
SELECT isgoodq(134217728) --returns false
Determines if the specified UInt32 expression represents an Uncertain Status Code.
SELECT isuncq(134217728) --returns false
SELECT isuncq(3221225472) --returns true
These functions operate on strings and provide related functionality.
Converts an Int64 expression representing ASCII values(s) to the equivalent character string. Each byte in the Int64 expression represents one character. As the input of this function is a 64-bit integer, the resulting string is limited to 8 characters.
SELECT asciitochar(65) --returns A
SELECT asciitochar(4276803) --returns ABC (0x414243)
Converts an Int64 expression representing Unicode values(s) to the equivalent Unicode character string. Each 2 bytes in the Int64 expression represent one Unicode character. As the input of this function is a 64-bit integer, the resulting string is limited to 4 characters.
SELECT asciitowchar(8721) --returns ∑
SELECT asciitowchar(1735478956) --returns 東京
Returns the Base64 encoded version of the input String expression.
SELECT base64encode('hello') --returns aGVsbG8=
Decodes the input Base64 encoded String expression.
SELECT base64decode('aGVsbG8=') --returns hello
Converts a String expression of one or more characters into a Int64 value representing the corresponding ASCII values. Each character in the input String expression represents one byte in the resulting Int64 value. As the output of this function is a Int64 value, the input expression is limited to 8 characters.
SELECT chartoascii('A') --returns 65
SELECT chartoascii('ABC') --returns 4276803
Compares two String expressions using the invariant culture, either with case sensitivity or case insensitivity. The syntax is:
compare(first_string_expression, second_string_expression, bool_case_sensitive_comparison)
The result is -1 if the first string is lesser than the second string, 1 if the first string is greater than the second string and 0 if the two strings are equal.
SELECT compare('a', 'A', true) --returns -1 (case-sensitive)
SELECT compare('A', 'a', true) --returns 1 (case-sensitive)
SELECT compare('a', 'a', true) --returns 0 (case-sensitive)
SELECT compare('a', 'A', false) --returns 0 (case-insensitive)
SELECT compare('diyarbakır', 'DİYARBAKIR', false) --returns -1 (case-insensitive, but because of the Turkish i, the first string is lesser than the second)
Compares two String expressions using the culture of the collation of the first string, either with case sensitivity or case insensitivity. If the COLLATE keyword is not specified, the culture of the data model’s collation will be used. The syntax is:
compareculture(first_string_expression, second_string_expression, bool_case_sensitive_comparison)
The result is -1 if the first string is lesser than the second string, 1 if the first string is greater than the second string and 0 if the two strings are equal.
SELECT compareculture('a', 'A', true) --returns -1 (case-sensitive)
SELECT compareculture('A', 'a', true) --returns 1 (case-sensitive)
SELECT compareculture('a', 'a', true) --returns 0 (case-sensitive)
SELECT compareculture('a', 'A', false) --returns 0 (case-insensitive)
SELECT compareculture('diyarbakır' COLLATE tr-tr_ci, 'DİYARBAKIR', false) --returns 0 (case-insensitive, but since the comparison is executed in the Turkish culture, the two strings result equal)
Takes N String expressions and produces a single String value representing the concatenation of the input expressions.
SELECT concat('a', 'b', 'c') --returns "abc"
Takes a String expression representing a separator and N String expressions to produce the concatenation of the N expressions separated by the specified separator.
Note: this function is the equivalent of the Expression Engine’s join function, however the JOIN keyword is reserved for joins in AnalytiX-BI.
SELECT concat_ws('/', 'a', 'b', 'c') --returns "a/b/c"
Returns the zero-based index of the first occurrence of a String expression inside another String expression, or -1 if the occurrence was not found. The search is case-sensitive. The function has three overloads:
indexof(string_expression_where_to_search, string_value_expression_to_find)
indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search)
indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search, number_of_characters_to_examine)
The string_value_expression_to_find parameter can be a single character or a multi-character string.
SELECT indexof('Condiments/Confections', 'o') --returns 1
SELECT indexof('Condiments/Confections', 'o', 2) --returns 12
SELECT indexof('Condiments/Confections', 'o', 2, 10) --returns -1
SELECT indexof('Condiments/Confections', 'dime', 2, 10) --returns 3
Returns the zero-based index of the first occurrence within a String expression of any character from another String expression, or -1 if no occurrence was not found. The search is case-sensitive. The function has three overloads:
indexofany(string_expression_where_to_search, set_of_characters_to_search_for)
indexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search)
indexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search, number_of_characters_to_examine)
When the set_of_characters_to_search_for parameter is specified as a String, each character in the string will be checked as an individual character.
SELECT indexofany('Condiments/Confections', 'no') --returns 1
SELECT indexofany('Condiments/Confections', 'no', 2) --returns 2
SELECT indexofany('Condiments/Confections', 'no', 3, 7) --returns 7
Returns the zero-based index of the N-th occurrence of a String expression inside another String expression, or -1 if the occurrence was not found. The search is case-sensitive. The function has three overloads:
indexofoccurrence (string_expression_where_to_search, string_value_expression_to_find, nth_occurrence_to_find)
indexofoccurrence (string_expression_where_to_search, string_value_expression_to_find, nth_occurrence_to_find, index_where_to_start_search)
indexofoccurrence (string_expression_where_to_search, string_value_expression_to_find, nth_occurrence_to_find, index_where_to_start_search, number_of_characters_to_examine)
SELECT indexofoccurrence('Condiments/Confections', 'on', 1) --returns 1
SELECT indexofoccurrence('Condiments/Confections', 'on', 2) --returns 12
SELECT indexofoccurrence('Condiments/Confections', 'on', 3) --returns 19
SELECT indexofoccurrence('Condiments/Confections', 'on', 1, 2) --returns 12
SELECT indexofoccurrence('Condiments/Confections', 'on', 1, 2, 10) --returns -1
Inverts the case of a String expression.
SELECT invertcase('aBc') --returns "AbC"
Returns the zero-based index of the last occurrence of a String expression inside another String expression, or -1 if the occurrence was not found. The search is case-sensitive. The function has three overloads:
indexof(string_expression_where_to_search, string_value_expression_to_find)
indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search)
indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search, number_of_characters_to_examine)
The string_value_expression_to_find parameter can be a single character or a multi-character string. When the index_where_to_start_search parameter is specified, the search starts at that character backwards for number_of_characters_to_examine characters – or to the beginning of the string if the parameter is not specified.
SELECT lastindexof('Condiments/Confections', 'o') --returns 19
SELECT lastindexof('Condiments/Confections', 'o', 18) --returns 12
SELECT lastindexof('Condiments/Confections', 'o', 18, 5) --returns -1
Returns the zero-based index of the last occurrence within a String expression of any character from another String expression, or -1 if no occurrence was not found. The search is case-sensitive. The function has three overloads:
lastindexofany(string_expression_where_to_search, set_of_characters_to_search_for)
lastindexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search)
lastindexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search, number_of_characters_to_examine)
When the set_of_characters_to_search_for parameter is specified as a String, each character in the string will be checked as an individual character. When the index_where_to_start_search parameter is specified, the search starts at that character backwards for number_of_characters_to_examine characters – or to the beginning of the string if the parameter is not specified.
SELECT lastindexofany('Condiments/Confections', 'no') --returns 20
SELECT lastindexofany('Condiments/Confections', 'no', 18) --returns 13
SELECT lastindexofany('Condiments/Confections', 'no', 10, 10) --returns 7
Returns the specified number of characters from the left side of a String Expression.
SELECT left('Category', 3) --returns "Cat"
Returns the length (number of characters) of a String expression.
SELECT len('Category') --returns 8
Performs a wildcard string search on a String expression. The syntax is:
like(string_expression_where_to_search, pattern_to_search_for, bool_case_sensitive_comparison)
The pattern_to_search_for parameter may contain wildcards. Wildcard characters include:
· ? (question mark). Matches any single character.
· * (asterisk). Matches any character zero or more times.
· # (pound). Matches any single digit (0-9).
· [charlist]. Matches any single character in charlist
· [!charlist]. Matches any single character that is not in charlist
To match a literal wildcard, enclose it in square brackets, i.e.: [*].
SELECT like('Category', 'cat*', false) --returns true
SELECT like('Category', 'cat*', true) --returns false
SELECT like('Category', 'c?t*', false), like('Cotton', 'c?t*', false) --both return true
SELECT like('12', '1#', false) --returns true for all two digit numbers starting with 1
SELECT like('cat', 'c[ao]t', false), like('cot', 'c[ao]t', false), like('cut', 'c[ao]t', false) --matches "cat", "cot" but not "cut"
SELECT like('cat', 'c[!a]t', false) --matches any three-letter word starting with "c" and ending with "t" but "cat"
SELECT like('hello?', '*[?]', false) --matches any word ending in question mark
Note: this function is available for compatibility with the Expression Engine however, in AnalytiX-BI, it is recommended to use the LIKE operator.
Makes a String expression lowercase. If the COLLATE keyword is not specified, the conversion is performed using the casing rules of the culture of the data model’s collation. This function produces the same result as the TOLOWER function.
SELECT lower('DİYARBAKIR') --returns "diyarbakir" in the en-us culture
SELECT lower('DİYARBAKIR' COLLATE tr-tr_ci) --returns "diyarbakır"
Returns the Unicode character with the specified integer code, as defined by the Unicode standard. This function is similar to the ASCIITOWCHAR function, but it only operates on a single character.
SELECT nchar(8721) --returns ∑
Returns the starting position of the first occurrence of a pattern in a specified String expression, or 0 if the pattern is not found. If the COLLATE keyword is not specified, the culture of the data model’s collation will be used for the comparisons.
The index result of this function is 1-based, meaning that a match at the first character will return 1 instead of 0, unlike other functions like INDEXOF. The syntax is:
patindex(pattern_to_search_for, string_expression_where_to_search)
The pattern_to_search_for parameter works the same way as the LIKE operator, and accepts the same patterns, with the difference that the % character must come before and follow the pattern (except when searching for first or last characters).
SELECT patindex('%t_g%', 'Category') --returns 3
The following example uses the ^ character to match the first character that is not a letter, digit or space.
SELECT patindex('%[^ 0-9A-z]%', 'Welcome!') --returns 8
The following examples show the usage of PATINDEX with the COLLATE keyword.
SELECT patindex('%I%', 'kır' COLLATE en-us_ci) --returns 0
SELECT patindex('%I%', 'kır' COLLATE tr-tr_ci) --returns 2
Returns whether the regular expression matches the input expression. This function has three overloads:
rgmatch(string_expression_where_to_search, regex_pattern_to_search_for)
rgmatch(string_expression_where_to_search, regex_pattern_to_search_for, index_where_to_start_search)
rgmatch(string_expression_where_to_search, regex_pattern_to_search_for, index_where_to_start_search, regex_options)
When the regex_options parameter is specified, it must be a combination of the following values:
· rg_culture_inv() Makes comparisons use the invariant culture.
· rg_ignore_case() Specifies case-insensitive matching.
· rg_ignore_pattern_ws() Eliminates unescaped white space from the pattern.
The following examples use a regular expression to match ZIP codes with 5 digits or 5+4 digits.
SELECT rgmatch('02035', '^\\d{5}(-\\d{4})?$') --returns true
SELECT rgmatch('02035-0000', '^\\d{5}(-\\d{4})?$') --returns true
The following examples show the usage of the start index.
SELECT rgmatch('Condiments', 'Con.*', 0) --returns true
SELECT rgmatch('Condiments', 'Con.*', 1) --returns false
The following examples show the usage of the options.
SELECT rgmatch('Condiments', 'con.*', 0) --returns false
SELECT rgmatch('Condiments', 'con.*', 0) --returns false
SELECT rgmatch('Condiments', 'con.*', 0, rg_ignore_case()) --returns true
SELECT rgmatch('Condiments', ' con.*', 0, rg_ignore_case() + rg_ignore_pattern_ws()) --returns true
Replaces all the instances of the string that match a regular expression pattern in a String expression with the specified replacement string. This function has two overloads:
rgreplace(string_expression_where_to_search, regex_pattern_to_search_for, string_expression_replacement_string)
rgreplace(string_expression_where_to_search, regex_pattern_to_search_for, string_expression_replacement_string, regex_options)
The following examples show usage of the RGREPLACE function.
SELECT rgreplace('--abc--adc--', 'a[a-z]c', 'xyz') --returns "--xyz--xyz---"
SELECT rgreplace('--abc--aDc--', 'a[a-z]c', 'xyz') --returns "--xyz--aDc---"
SELECT rgreplace('--abc--aDc--', 'a[a-z]c', 'xyz', rg_ignore_case()) --returns "--xyz--xyz---"
Replaces all the instances of the string that match the specified search String expression in a String expression with the replacement String expression. The function has the following syntax:
replace(string_expression_where_to_search, string_expression_to_search_for, string_expression_replacement_string)
SELECT replace('--abc--abc--adc--', 'abc', 'xyz') --returns "--xyz--xyz--adc--"
Reverses a string.
SELECT reverse('tab') --returns "bat"
Returns the specified number of characters from the right side of a String Expression.
SELECT right('Category', 3) --returns "ory"
Extracts a portion of a String expression. The function has two overloads:
substring(string_expression_input, index_where_to_start_extraction)
substring(string_expression_input, index_where_to_start_extraction, number_of_characters_to_extract)
When the number_of_characters_to_extract parameter is not specified, the substring starts from index_where_to_start_extraction and ends at the end of string_expression_input. If index_where_to_start_extraction goes over the length of string_expression_input, an empty string is returned.
SELECT substring('hello world', 6) --returns "world"
SELECT substring('hello world', 3, 5) --returns "lo wo"
SELECT substring('hello world', 12) --returns an empty string
Makes a String expression lowercase. If the COLLATE keyword is not specified, the conversion is performed using the casing rules of the culture of the data model’s collation. This function produces the same result as the LOWER function.
SELECT tolower('DİYARBAKIR') --returns "diyarbakir" in the en-us culture
SELECT tolower('DİYARBAKIR' COLLATE tr-tr_ci) --returns "diyarbakır"
Makes a String expression uppercase. If the COLLATE keyword is not specified, the conversion is performed using the casing rules of the culture of the data model’s collation. This function produces the same result as the UPPER function.
SELECT toupper('diyarbakır') --returns "DIYARBAKIR"
SELECT toupper('diyarbakır' COLLATE tr-tr_ci) --returns "DİYARBAKIR"
Removes all leading and trailing occurrences of the specified set of characters from an input String expression. The function has two overloads:
trim(string_expression_to_trim)
trim(string_expression_to_trim, characters_to_trim)
If the characters_to_trim parameter is not specified, all leading and trailing spaces will be removed from string_expression_to_trim. Otherwise, each character specified in characters_to_trim will be removed from string_expression_to_trim if it is either leading or trailing.
SELECT 'a' + trim(' b ') + 'c' --returns "abc"
SELECT 'a' + trim('#b$', '#$') + 'c' --returns "abc"
Removes all leading occurrences of the specified set of characters from an input String expression. The function has two overloads:
trimleft(string_expression_to_trim)
trimleft(string_expression_to_trim, characters_to_trim)
If the characters_to_trim parameter is not specified, all leading spaces will be removed from string_expression_to_trim. Otherwise, each character specified in characters_to_trim will be removed from string_expression_to_trim if it is leading.
SELECT 'a' + trimleft(' b ') + 'c' --returns "ab c"
SELECT 'a' + trimleft('#b$', '#$') + 'c' --returns "ab$c"
Removes all trailing occurrences of the specified set of characters from an input String expression. The function has two overloads:
trimright(string_expression_to_trim)
trimright(string_expression_to_trim, characters_to_trim)
If the characters_to_trim parameter is not specified, all trailing spaces will be removed from string_expression_to_trim. Otherwise, each character specified in characters_to_trim will be removed from string_expression_to_trim if it is trailing.
SELECT 'a' + trimright(' b ') + 'c' --returns "a bc"
SELECT 'a' + trimright('#b$', '#$') + 'c' --returns "a#bc"
Makes a String expression uppercase. If the COLLATE keyword is not specified, the conversion is performed using the casing rules of the culture of the data model’s collation. This function produces the same result as the TOUPPER function.
SELECT upper('diyarbakır') --returns "DIYARBAKIR"
SELECT upper('diyarbakır' COLLATE tr-tr_ci) --returns "DİYARBAKIR"
Encodes a URL String expression.
SELECT urlencode('a parameter value') --returns "a+parameter+value"
Decodes a URL String expression.
SELECT urldecode('a+parameter+value') --returns "a parameter value"
Converts a String expression of one or more characters into a Int64 value representing the corresponding Unicode values. Each character in the input String expression represents two bytes in the resulting Int64 value. As the output of this function is a Int64 value, the input expression is limited to 4 characters.
SELECT wchartoascii('∑') --returns 8721
SELECT wchartoascii('東京') --returns 1735478956
These functions are for creating instances of TimeSpan and extracting values form them.
Returns a TimeSpan value from a Double expression that represents a number of days, either integral or fractional.
SELECT fromdays(1) -- returns 1.00:00:00
SELECT fromdays(1.5) -- returns 1.12:00:00
Returns a TimeSpan value from a Double expression that represents a number of hours, either integral or fractional.
SELECT fromhours(1) -- returns 01:00:00
SELECT fromhours(1.5) -- returns 01:30:00
Returns a TimeSpan value from a Double expression that represents a number of milliseconds, either integral or fractional.
SELECT frommilliseconds(1) -- returns 00:00:00.0010000
SELECT frommilliseconds(1.5) -- returns 00:00:00.0015000
Returns a TimeSpan value from a Double expression that represents a number of minutes, either integral or fractional.
SELECT fromminutes(1) -- returns 00:01:00
SELECT fromminutes(1.5) -- returns 00:01:30
Returns a TimeSpan value from a Double expression that represents a number of seconds, either integral or fractional.
SELECT fromseconds(1) -- returns 00:00:01
SELECT fromseconds(1.5) -- returns 00:00:01.5000000
Returns a Double value representing the total number of days in a TimeSpan expression.
SELECT totaldays('1.00:00:00') --returns 1
SELECT totaldays('1.12:00:00') --returns 1.5
Returns a Double value representing the total number of hours in a TimeSpan expression.
SELECT totalhours('01:00:00') --returns 1
SELECT totalhours('01:30:00') --returns 1.5
Returns a Double value representing the total number of milliseconds in a TimeSpan expression.
SELECT totalmilliseconds('00:00:01') --returns 1000
SELECT totalmilliseconds('00:00:00.0015') --returns 1.5
Returns a Double value representing the total number of minutes in a TimeSpan expression.
SELECT totalminutes('00:01:00') --returns 1
SELECT totalminutes('00:01:30') --returns 1.5
Returns a Double value representing the total number of seconds in a TimeSpan expression.
SELECT totalseconds('00:00:01') --returns 1
SELECT totalseconds('00:00:01.500') --returns 1.5
These function check if an expression can is of – or can be converted to – as specific data type.
Returns true if the specified expression can be converted to a Boolean, otherwise false. If the input expression is a string, possible conversion to Boolean will be checked using the invariant culture.
SELECT isboolean(false) --returns true
SELECT isboolean(1) --returns true
SELECT isboolean('a') --returns false
Returns true if the specified expression can be converted to a Boolean, otherwise false. If the input expression is a string and the COLLATE keyword is not specified, the conversion is checked using the culture of the data model’s collation.
SELECT isbooleanculture(false) --returns true
SELECT isbooleanculture(1) --returns true
SELECT isbooleanculture('1,1') --returns false in the en-us culture
SELECT isbooleanculture('1,1' COLLATE it-it_ci) --returns true
Returns true if the specified expression can be converted to a DateTime, otherwise false. If the input expression is a string, possible conversion to DateTime will be checked using the invariant culture.
SELECT isdatetime(now()) --returns true
SELECT isdatetime(1) --returns true
SELECT isdatetime('09/29/2021') --returns true
SELECT isdatetime('29/09/2021') --returns false
Returns true if the specified expression can be converted to a DateTime, otherwise false. If the input expression is a string and the COLLATE keyword is not specified, the conversion is checked using the culture of the data model’s collation.
SELECT isdatetimeculture(now()) --returns true
SELECT isdatetimeculture(1) --returns true
SELECT isdatetimeculture('09/29/2021') --returns true
SELECT isdatetimeculture('29/09/2021') --returns false in the en-us culture
SELECT isdatetimeculture('29/09/2021' COLLATE it-it_ci) --returns true
Returns true if the specified expression can be converted to a Double, otherwise false. If the input expression is a string, possible conversion to Double will be checked using the invariant culture.
SELECT isnumber(1) --returns true
SELECT isnumber(1.1) --returns true
SELECT isnumber(now()) --returns true
SELECT isnumber(CAST('00:10:00' AS TimeSpan)) --returns true
SELECT isnumber('1.1') --returns true
SELECT isnumber('1,1') --returns false
Returns true if the specified expression can be converted to a Double, otherwise false. If the input expression is a string and the COLLATE keyword is not specified, the conversion is checked using the culture of the data model’s collation.
SELECT isnumberculture(1) --returns true
SELECT isnumberculture(1.1) --returns true
SELECT isnumberculture(now()) --returns true
SELECT isnumberculture(CAST('00:10:00' AS TimeSpan)) --returns true
SELECT isnumberculture('1.1') --returns true in the en-us culture
SELECT isnumberculture('1,1') --returns false in the en-us culture
SELECT isnumberculture('1,1' COLLATE it-it_ci) --returns true
Returns true if the specified expression can be converted to a TimeSpan, otherwise false. If the input expression is a string, possible conversion to TimeSpan will be checked using the invariant culture.
SELECT istimespan(gettimeofday(now())) --returns true
SELECT istimespan(1) --returns true
SELECT istimespan('00:01:10.123') --returns true
SELECT istimespan('00:01:10,123') --returns false
Returns true if the specified expression can be converted to a TimeSpan, otherwise false. If the input expression is a string and the COLLATE keyword is not specified, the conversion is checked using the culture of the data model’s collation.
SELECT istimespanculture(gettimeofday(now())) --returns true
SELECT istimespanculture(1) --returns true
SELECT istimespanculture('00:01:10.123') --returns true in the en-us culture
SELECT istimespanculture('00:01:10,123') --returns false in the en-us culture
SELECT istimespanculture('00:01:10,123' COLLATE it-it_ci) --returns true
See Also: