Applying Functions to Create a New Column

You can perform calculations on the data streaming in the pipeline, and also add new fields into the stream using in-built functions of the Expression Builder.

To launch the Expression Builder, click fx in the Live Output table.

Note:

Currently, you can use expressions only within a query stage.

Adding a Constant Value Column

A constant value is a simple string or number. No calculation is performed on a constant value. Enter a constant value directly in the expression builder to add it to the live output table.

Description of the illustration expr_constant_value.png

Using Functions

You can select a CQL Function from the list of available functions and select the input parameters. Make sure to begin the expression with ”=”. Click Apply to apply the function to the streaming data.

Example expression using functions:

=float((CanceledOrdersFloat/NewOrdersFloat) * 100.0) 

Description of the illustration list_of_functions.png

You can see custom functions in the list of available functions when you add/import a custom jar in your pipeline.

For a list of supported functions, see unresolvable-reference.html#GUID-B515EA58-E4F0-4523-B4D5-EFFD8E46F079 .

Using Bessel Functions

The mathematical cylinder functions for integers are known as Bessel functions.

The following Bessel functions are supported in this release:

Function Name	Description
BesselI0(x)	Returns the modified Bessel function of order 0 of the double argument as a double
BesselI0_exp(x)	Returns the exponentially scaled modified Bessel function of order 0 of the double argument as a double
BesselI1(x)	Returns the modified Bessel function of order 1 of the double argument as a double
BesselI1_exp(x)	Returns the exponentially scaled modified Bessel function of order 1 of the double argument as a double
BesselJ(x,x)	Returns the Bessel function of the first kind of order n of the argument as a double
BesselK(x,x)	Returns the modified Bessel function of the third kind of order n of the argument as a double
BesselK0_exp(x)	Returns the exponentially scaled modified Bessel function of the third kind of order 0 of the double argument as a double
BesselK1_exp(x)	Returns the exponentially scaled modified Bessel function of the third kind of order 1 of the double argument as a double
BesselY(x)	Returns the Bessel function of the second kind of order n of the double argument as a double

BesselI0

Returns the modified Bessel function of order 0 of the input argument.

The input arguments can be one of the following data types: double, float.

Returned value type will be double.

Function	Result
besselIO(65)	8.403039845625433E26
besselIO(3125.2)	1.07389541368045088E17

BesselIO_exp

Returns the exponentially scaled modified Bessel function of order 0 of the double argument as a double.

The input argument can be one of the following data type: double, integer, float. Returned value type will be double.

Function	Result
besselIO_exp(1451.44)	8.113723742037748E23

BesselI1(value1)

Function returns the modified Bessel function of order 1 of the double argument.

The input arguments can be one of the following data types: double, integer, float.

The returned value type will be double.

Function	Result
besselI1(432.98)	2.1043808863643512E186
besselI1(31)	2.055972795294565E12

BesselI1_exp(value1)

Function returns the exponentially scaled modified Bessel function of order 1, of the input argument.

The input arguments can be one of the following types: double, integer, float.

Returned value type will be double.

Function	Result
besselI1_exp(99)	0.03994284829937756

BesselK0_exp(value1)

Function returns the exponentially scaled modified Bessel function of the third kind of order 0.

Input value can be one of the following types: double, integer, float.

Returned value type will be double.

Function	Result
Besselk0_exp(3.6)	0.6404559726736455

BesselIK1_exp(value1)

Function returns the exponentially scaled modified Bessel function of the third kind of order 1.

Input value can be one of the following types: double, integer, float.

Returned value type will be double.

Function	Result
BesselIK1_exp(72)	0.14847048263652857
BesselIK1_exp(3.6)	0.7244606719817783

BesselY(value1, value2)

Function returns the Bessel function of the second kind of order n of the input argument.

Value 1 can be of the following types: integer.

Value 2 can be of the following types: double, integer, float.

Returned value type will be double.

Function	Result
BesselY(30,2.2)	-1.6816755062290252E29

BesselJ(value1, value2)

Function returns the Bessel function of the first kind of order n of the argument.

The input arguments can be one of the following data types:

• Value1 can be one of the following types: integer.
• Value 2 can be one of the following types: double, integer, float.

Returned value type will be double.

Function	Result
besselJ(4,3.3)	0.1742753869717833

BesselK(value1,value2)

Function returns the modified Bessel function of the third kind of order n of the input argument.

Value1 can be one of the following types: integer.

Value 2 can be one of the following types: double, integer, float.

Returned value type will be double.

Function	Result
BesselK(30,2)	4.271125754887687E30

Using Conversion Functions

The conversion functions help in converting values from one data type to other.

The following conversion functions are supported in this release:

Function Name	Description
bigdecimal(value1)	Converts the given value to bigdecimal
boolean(value1)	Converts the given value to logical
date(value1,value2)	Converts the given value to datetime
double(value1)	Converts the given value to double
float(value1)	Converts the given value to float
int(value1)	Converts the given value to integer
long(value1)	Converts the given value to long
string(value1,value2)	Converts the given value to string

bigdecimal(value1)

Converts the input argument value to big decimal. The input argument can be one of the following data types: big integer, number, double, integer, text, float. Returned value type will be number.

Function	Result
bigdecimal(60)	6E+1
bigdecimal(32)	32

boolean(value1)

Converts the input argument value to logical. The input argument can be one of the following data type: big integer or integer. Returned value type will be Boolean.

Examples

Function	Result
boolean(5)	TRUE
boolean(0)	FALSE
boolean(NULL)	TRUE
boolean()	TRUE
boolean(-5)	TRUE

double(value1)

Converts the input argument value to double. The input argument can be one of the following data types: integer, big integer, double, text or float. Returned value type will be double.

Examples

Function	Result
double(3.1406)	3.1405999660491943
double(1234.56)	1234.56005859375

float(value1)

Converts the input argument value to float. The input argument can be one of the following data types: integer, big integer, double, text or float. Returned value will be a single-precision floating-point number.

Examples

Function	Result
float(1.67898989395)	1.6789899
float(1.796709289)	1.7967093
float(12.60508090750)	12.605081

int(value1)

Converts the input argument value to integer. The input argument can be one of the following types: integer, text. Returned value type will be integer.

Function	Result
int(50/3)	16

long()

Converts the input argument value to long. The input argument can be one of the following types: big integer, integer, text, float, timestamp. Returned value type will be big integer.

Function	Result
long(5039505078907524)	5039505078907524
long(22)	22

string(value1, value2)

Conversion to string.

Value 1 can be one of the following types: intervalym, big integer, number, boolean, double, interval, integer, float, timestamp. Required.

Value 2 is output date format. It's required argument for value1 of type timestamp. Value can be one of the following types: text. Optional.

Returned value will be of type text.

Function	Result
string(transaction_time,"hh-mm-ss") , where transaction_time is 12/19/2016 12:23:04	12-23-04
string(transaction_time,"M-DD-YY") , where transaction_time is 12/19/2016 12:23:04	12-19-16

Using Date Functions

The following date functions are supported in this release:

Function Name	Description
day(date)	Returns day of the date
eventtimestamp()	Returns event timestamp from stream
hour(date)	Returns hour of the date
minute(date)	Returns minute of the date
month(date)	Returns month of the date
nanosecond(date)	Returns nanosecond of the date
second(date)	Returns second of the date
systimestamp()	Returns the system’s timestamp on which the application is running
timeformat(value1,value2)	Returns the provided timestamp in required time format
year(date)	Returns year of the date

Acceptable Formats for Timestamp Values

This sections lists the acceptable formats for timestamp values in Oracle Stream Analytics.

Format	Example Values
MM/dd/yyyy HH:mm:ss.SSSS	3/21/2018 11:14:23.1111
MM/dd/yyyy HH:mm:ss.SSS	3/21/2018 11:14:23.111
MM/dd/yyyy HH:mm:ss.SS	3/21/2018 11:14:23.11
MM/dd/yyyy HH:mm:ss.S	3/21/2018 11:14:23.1
MM/dd/yyyy HH:mm:ss	3/21/2018 11:14:23
MM/dd/yyyy HH:mm	3/21/2018 11:14
MM/dd/yyyy HH	3/21/2018 11
MM/dd/yyyy	3/21/2018
MM-dd-yyyy HH:mm:ss.SSSS	11-21-2018 11:14:23.1111
MM-dd-yyyy HH:mm:ss.SSS	11-21-2018 11:14:23.111
MM-dd-yyyy HH:mm:ss.SS	11-21-2018 11:14:23.11
MM-dd-yyyy HH:mm:ss.S	11-21-2018 11:14:23.1
MM-dd-yyyy HH:mm:ss	11-21-2018 11:14:23
MM-dd-yyyy HH:mm	11-21-2018 11:14
MM-dd-yyyy HH	11-21-2018 11
MM-dd-yyyy	11-21-2018
dd-MMM-yy hh.mm.ss.SSSSSS a	11-Jan-18 11.14.23.111111 AM
dd-MMM-yy hh.mm.ss.SSSS	11-Jan-18 11.14.23.1111
dd-MMM-yy hh.mm.ss.SSS	11-Jan-18 11.14.23.111
dd-MMM-yy hh.mm.ss.SS	11-Jan-18 11.14.23.11
dd-MMM-yy hh.mm.ss.S	11-Jan-18 11.14.23.1
dd-MMM-yy hh.mm.ss	11-Jan-18 11.14.23
dd-MMM-yy hh.mm	11-Jan-18 11.14
dd-MMM-yy hh	11-Jan-18 11
dd-MMM-yy	11-Jan-18
dd/MMM/yy	15/MAR/18
yyyy-MM-dd HH:mm:ss.SSSSSS	2018-03-5 15:16:0.756000 +5:30,   2018-03-5 15:16:0.756000
yyyy-MM-dd HH.mm:.ss.SSSSSS	2018-03-5 15.16.0.756000 +5:30,   2018-03-5 15.16.0.756000
yyyy-MM-dd HH:mm:ss	2018-03-5 15:16:0;   2018-03-5 15:16:0 +5:30
yyyy-MM-dd HH.mm.ss	2018-03-5 15.16.0;   2018-03-5 15.16.0 +5:30
yyyy-MM-dd HH:mm	2018-03-5 15:16;   2018-03-5 15:16 +5:30
yyyy-MM-dd HH.mm	2018-03-5 15.16;   2018-03-5 15.16 +5:30
yyyy-MM-dd HH	2018-03-5 15
yyyy-MM-dd	2018-03-5
HH:mm:ss	11:14:14 PST
yyyy-MM-dd'T'HH:mm:ss'.'SSS	2018-03-04T12:08:56.235
yyyy-MM-dd'T'HH:mm:ss'.'SSSZ	2018-03-04T12:08:56.235-0700
yyyy-MM-dd'T'HH:mm:ss'.'SSSz	2018-03-04T12:08:56.235 PDT
yyyy-MM-dd'T'HH:mm:ss	2018-03-04T12:08:56
yyyy-MM-dd'T'HH:mm:ssZ	2018-03-04T12:08:56-0700
yyyy-MM-dd'T'HH:mm:ssz	2018-03-04T12:08:56 PDT

Day(date)

day(date) function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the day in the timestamp represented by this date object. Returns a big integer indicating the day represented by this date.

Examples

Function	Result
day(transaction-time), where transaction_time is 12/19/2016 12:22:48	19

eventtimestamp(value1)

Event timestamp from stream.

Returned value will be of type timestamp.

Function	Result
eventtimestamp() ,	4/4/2019 16:40:57

hour(date)

hour(date) function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the hour in the time represented by this date object. Returns a big integer indicating the hour of the time represented by this date.

Examples

Function	Result
hour(12/06/17 09:15:22 AM)	09
hour(2015:07:21 12:45:35 PM)	12

minute(date)

minute(date) function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the minutes in the time represented by this date object. Returns a big integer indicating the minutes of the time represented by this date.

Examples

Function	Result
minute(12/06/17 09:15:22 AM)	15
minute(2015:07:21 12:45:35 PM)	45

month(date)

month(date) function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the month of the year that contains or begins with the instant in time represented by this date object. Returns a big integer indicating the month of the year represented by this date.

Examples

Function	Result
month(12/06/17 09:15:22 AM)	12
month(2017:09:23 11:20:25 AM)	9

nanosecond(value1)

Extracts and returns the current fractional part of second from date.

Value 1 can be one of the following types: timestamp.

Returned value will be of type big integer.

Function	Result
nanosecond(transaction_time), 12/19/2016 12:22:57	719978080

systemtimestamp(value1)

Returns the current system time.

Returned value will be of type timestamp.

Function	Result
systemtimestamp() ,	4/4/2019 17:06:14

timeformat(value1, value2)

Event Formatted time.

Value 1 can be one of the following types: timestamp.

Value 2 can be one of the following types: text.

Returned value will be of type text.

Function	Result
timeformat(transaction_time, "M-dd-yy"), where calc is 12/19/2016 12:22:46	12-19-16
timeformat(transaction_time, "DAY"), where transaction_time is 12/19/2016 12:22:44	Monday

Year(date)

year(date) function takes as an argument any one of the following data types: time interval or time stamp. The returned value represents the year of the instant in time represented by this date object. Returns a big integer indicating the year represented by this date.

Examples

Function	Result
year(12/06/17 09:15:22 AM)	17
year(2015:07:21 12:45:35 PM)	2015

Using Geometry Functions

The Geometry functions allow you to convert the given values into a geometrical shape.

The following interval functions are supported in this release:

Function Name	Description
CreatePoint(lat,long,SRID)	Returns a 2–dimensional point type geometry from the given latitude and longitude. The default SRID is 8307. The return value is of the datatype sdo geometry.
distance(lat1,long1,lat2,long2,SRID)	Returns distance between the first set of latitude, longitude and the second set of latitude, longitude values. The default SRID is 8307. Note: Only SRID 8307 is supported in the current release. The return value is of the datatype double.

CreatePoint(value1, value2, value3)

createPoint(lat,long,SRID) - Function Returns a 2d point type geometry, default SRID is 8307.

Value 1: Latitude - Value can be one of the following types: number, double, float. Required.

Value 2: Longitude - Value can be one of the following types: number, double, float.

Value 3: SRID - Value can be one of the following types: integer.

Returned value type will be sdo geometry.

Function	Result
CreatePoint(78995333342435,-122.4005650002481937,8307)	point

distance(lat1, long1, lat2, long2,SRID)

Function Returns distance between lat1/long1 and lat2/long2, default SRID is 8307.

Value 1: Latitude1 - Value can be one of the following types: number, double, float.

Value 2: Longitude1 - Value can be one of the following types: number, double, float.

Value 3: Latitude2 - Value can be one of the following types: number, double, float.

Value 4: Longitude2 - Value can be one of the following types: number, double, float.

Value 5: SRID - Value can be one of the following types: integer.

Returned value will be of type double.

Function	Result
distance(37.78371333337545, -122.4052500001069, 37.78371333337545, 37.78371333337545, 8307)	1.1394718018250743E7

Using Interval Functions

The Interval functions help you in calculating time interval from given values.

The following interval functions are supported in this release:

Function Name	Description
dsintervaltonum (c1 INTERVAL DAY TO SECOND, c2 char)	Converts the given value to a numeric value. User must provide unit for the output numeric value as second argument to this function. Allowed values for the unit is: DAY,HOUR,MINUTE,SECOND
numtodsinterval(n,interval_unit)	Converts the given value to an INTERVAL DAY TO SECOND literal. The value of the interval_unit specifies the unit of n and must resolve to one of the string values: DAY, HOUR, MINUTE, or SECOND. The return value is of the datatype interval.
numtoyminterval(n,interval_unit)	Converts the given value to an INTERVAL YEAR TO MONTHliteral. The value of the interval_unitspecifies the unit of nand must resolve to one of the following string values: YEAR, MONTH.
to_dsinterval(string)	Converts a string in format DD HH:MM:SS into a INTERVAL DAY TO SECOND data type. The DD indicates the number of days between 0 to 99. The HH:MM:SS indicates the number of hours, minutes and seconds in the interval from 0:0:0 to 23:59:59.999999. The seconds part can accept upto six decimal places. The return value is of the datatype interval.
to_yminterval(string)	Converts a string in format YY-MM into a INTERVAL YEAR TO MONTH data type. The YYpart indicates the number of years between 0 to 99. The MMpart indicates the number of months between 0-11. The return value is of the datatype interval.
ymintervaltonum(c1 INTERVAL YEAR TO MONTH, c2 char)	Converts the given value to a numeric value. User must provide unit for the output numeric value as second argument to this function. Allowed values for the unit is: YEAR,MONTH

dsintervaltonum(value1, value 2)

DSINTERVALTONUM(c1 INTERVAL DAY TO SECOND, c2 char) - Function will convert interval value(c1) into a numeric value. User must provide unit for the output numeric value as second argument to this function. Allowed values for the unit is: DAY,HOUR,MINUTE,SECOND

Input value 1 can be one of the following types: interval.

Input value 2 can be one of the following types: text.

Returned value will be of type double.

Function	Result
dsintervaltonum(calc_7, "MINUTE") ,	301.0
dsintervaltonum(calc_7, "DAY")	5.016666666666667

numtodsinterval(value1, value2)

Function converts n to an INTERVAL DAY TO SECOND literal.The value for interval_unit specifies the unit of n and must resolve to one of the following string values:DAY,HOUR,MINUTE,SECOND.

Value 1 can be one of the following types: big integer, double, integer, float.

Value 2 can be one of the following types: text.

Returned value will be of type interval.

Function	Result
numtodsinterval(34, "MONTH")	2 yy 10 mm
numtodsinterval(26.5, "HOUR")	1 dd 2 hr 30 mm 0 sec
numtodsinterval(1230, "MINUTE")	00 dd 20 hr 30 mm 0 sec
numtodsinterval(1, "DAY")	1 dd 0 hr 0 mm 0 sec

numtoyminterval(value1, value 2)

NUMTOYMINTERVAL(n,interval_unit) - Function converts n to an INTERVAL YEAR TO MONTH literal. The value for interval_unit specifies the unit of n and must resolve to one of the following string values:YEAR, MONTH.

Value 1 can be one of the following types: big integer, double, integer, float.

Value 2 can be one of the following types: text.

Returned value will be of type intervalym.

Function	Result
numtoyminterval(10.5, "YEAR")	10 yy 6 mm
numtoyminterval(34, "MONTH")	2 yy 10 mm

to_dsinterval(value1)

Function converts a string in format 'DD HH:MM:SS' into a INTERVAL DAY TO SECOND data type. The DD part indicates the number of days between 0 to 99. The HH:MM:SS part indicates the number of hours, minutes and seconds in the interval from 0:0:0 to 23:59:59.999999. The second part can accept upto 6 decimal places.

Input value can be one of the following types: text.

Returned value will be of type interval.

Function	Result
to_dsinterval("02 23:34:12")	2 dd 23hr 34mm 12 sec

to_yminterval(value1)

Function converts a string in format 'YY-MM' into a INTERVAL YEAR TO MONTH data type.The YY part indicates the number of years between 0 to 99. The MM part indicates the number of months between 0-11.

Value can be one of the following types: text.

Returned value type will be intervalym.

Function	Result
to_yminterval("94-3")	94 yy 3 mm

ymintervaltonum(value1, value2)

Function converts interval value(c1) into a numeric value. You must provide the unit for the output numeric value as the second argument to this function. Allowed values for the unit are: YEAR,MONTH.

Value 1 can be one of the following types: intervalym.

Value 2 can be one of the following types: text.

Returned value type will be double.

Function	Result
ymintervaltonum(94yy 5mm,"MONTH')	1133.0

Using Math Functions

The math functions allow you to perform various mathematical operations and calculations ranging from simple to complex.

The following math functions are supported in this release:

Function Name	Description
IEEEremainder(value1,value2)	Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard
abs(value1)	Returns the absolute value of a number
acos(value1)	Returns arc cosine of a value
asin(value1)	Returns arc sine of a value
atan(value1)	Returns arc tangent of a value
atan2(arg1,arg2)	Returns polar angle of a point (arg2, arg1)
binomial(base,power)	Returns binomial coefficient of the base raised to the specified power
bitMaskWithBitsSetFromTo(x)	BitMask with BitsSet (From, To)
cbrt(value1)	Returns cubic root of the specified value
ceil(value1)	Rounds to ceiling
copySign(value1,value2)	Returns the first floating-point argument with the sign of the second floating-point argument
cos(value1)	Returns cosine of a value
cosh(value1)	Returns cosine hyperbolic of a value
exp(x)	Returns exponent of a value
expm1(x)	More precise equivalent of exp(x); Returns 1 when x is around zero
factorial(value1)	Returns factorial of a natural number
floor(value1)	Rounds to floor
getExponent(value1)	Returns the unbiased exponent used in the representation of a double
getSeedAtRowColumn(value1,value2)	Returns a deterministic seed as an integer from a (seemingly gigantic) matrix of predefined seeds
hash(value1)	Returns an integer hashcode for the specified double value
hypot(value1,value2)	Returns square root of sum of squares of the two arguments
leastSignificantBit(value1)	Returns the least significant 64 bits of this UUID's 128 bit value
log(value1,value2)	Calculates the log value of the given argument to the given base, where value 1 is the value and value 2 is the base
log1(value1)	Returns the natural logarithm of a number
log10(value1)	Calculates the log value of the given argument to base 10
log2(value1)	Calculates the log value of the given argument to base 2
logFactorial(value1)	Returns the natural logarithm (base e) of the factorial of its integer argument as a double
longFactorial(value1)	Returns the factorial of its integer argument (in the range k >= 0 && k < 21) as a long
maximum(value1,value2)	Returns the maximum of 2 arguments
minimum(value1,value2)	Returns the minimum of 2 arguments
mod(value1,value2)	Returns modulo of a number
mosttSignificantBit(value1)	Returns the most significant 64 bits of this UUID's 128 bit value
nextAfter(value1,value2)	Returns the floating-point number adjacent to the first argument in the direction of the second argument
nextDown(value1)	Returns the floating-point value adjacent to the input argument in the direction of negative infinity
nextUp(value1)	Returns the floating-point value adjacent to the input argument in the direction of positive infinity
Pow(m,n)	Returns m raised to the nth power
rint(value1)	Returns the double value that is closest in value to the argument and is equal to a mathematical integer
round(value1)	Rounds to the nearest integral value
Scalb(d,scaleFactor)	Returns d × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set
signum(value1)	Returns signum of an argument as a double value
sin(value1)	Returns sine of a value
sinh(value1)	Returns sine hyperbolic of a value
sqrt(value1)	Returns square root of a value
stirlingCorrection(value1)	Returns the correction term of the Stirling approximation of the natural logarithm (base e) of the factorial of the integer argument as a double
tan(value1)	Returns tangent of a value
tanh(value1)	Returns tangent hyperbolic of a value
toDegrees(value1)	Converts the argument value to degrees
toRadians(value1)	Returns the measurement of the angle in radians
ulp(value1)	Returns the size of an ulp of the argument

IEEEremainder(value1, value1)

Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard : IEEEREMAINDER

Value 1 can be one of the following types: double.

Value 2 can be one of the following types: double

Returned value type will be double.

Function	Result
IEEEremainder(8809,8808)	-1.0

abs(value1)

Returns the Absolute value of the input argument.

Input value can be one of the following types: number, big integer, double, integer, float.

Returned value type will be the same as the input argument type.

Function	Result
abs(1234.560789)	1234.56078
abs(0.67)	0.6700000166893005

acos(value1)

Returns the Arc cosine of a value.

Value can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
acos(0.5)	1.0471975511965979

asin(value1)

Computes the arc sine of a value.

Value 1 can be one of the following types: big integer, double, integer, float.

Returned value type will be double.

Function	Result
asin(0.5)	0.5235987755982989

atan(value1)

Returns the arc tangent of the input value.

Input value can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
atan(34)	1.5413930385908916

atan2

Returns the polar angle of a point (value2, value1).

Value 1 can be one of the following types: big integer, double, integer, float.

Value 2 can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
atan2(8681.44, 8682.44)	0.7853405725825559

binomial(base, power)

Returns the Binomial coefficient of the input base and power values.

Value 1 can be one of the following types: big integer, double, integer, float.

Value 2 can be one of the following types: big integer, integer.

Returned value will be of type double.

Function	Result
binomial(8609.4, 38)	5.955734227594785E104

bitMaskWithBitsSetFromTo(value1, value2)

Value 1 can be one of the following types: integer.

Value 2 can be one of the following types: integer.

Returned value will be of type double.

Function	Result
bitMaskWithBitsSetFromTo(23, 23)	8388608.0

cbrt()

Returns the cubic root of a value.

Value 1 can be one of the following types: big integer, double, integer, float.

Returned value type will be double.

Function	Result
cbrt(27)	3

ceil()

Round to ceiling.

The input arguments can be one of the following data types: double, float.

Returned value type will be float.

Function	Result
ceil(65)	65.0

copySign()

Function returns the first floating-point argument with the sign of the second floating-point argument.

Value1 can be one of the following types: double, float.

Returned value type will be double, float.

Function	Result
copySign(3.0, -4.0))	-3.0

cos(value1)

Returns the cosine of a value

Value 1 can be one of the following types: big integer, double, integer, float.

Returned value type will be double.

Function	Result
cos(7740.8)	0.9964325256163951

cosh(value1)

Returns the Cosine hyperbolic of a value.

Value 1 can be one of the following types: big integer, double, integer, float.

Function	Result
cosh(0.5)	1.1276259652063807

exp(value1, value2)

Returns the exponent of a value.

Value 1 can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
exp(10)	22026.465794806718

expm1(value1)

Returns the more precise equivalent of Exp(x)-1 when x is around zero.

Value can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
expm1(0.7)	1.0137526834646737

factorial(value1)

Returns the Factorial of a natural.

Value 1 can be one of the following types: integer.

Returned value type will be double.

Function	Result
factorial(6)	720.0

floor(value1)

Value can be one of the following types: big integer, double, integer, float.

Returned value will be of type float.

Function	Result
floor(0.567)	0.0

GetExponent(value1)

Function returns the unbiased exponent used in the representation of a double.

Value 1 can be one of the following types: double, float.

Returned value will be of type integer.

Function	Result
getExponent(10.0)	3.0

getSeedAtRowColumn(value1, value2)

Returns a deterministic seed as an integer from a (seemingly gigantic) matrix of predefined seeds : GETSEEDATROWCOLUMN

Value 1 can be one of the following types: integer.

Value 2 can be one of the following types: integer.

Returned value will be of type integer.

Function	Result
getSeedAtRowColumn(48, 2)	443210610

hash(value1)

Function returns an integer hashcode for the specified value.

Value can be one of the following types: big integer, double, integer, float.

Returned value will be of type integer.

Function	Result
hash(8.1)	1.33589862E9

hypot(value1, value2)

Square root of sum of squares of the two arguments.

Value 1 can be one of the following types: big integer, double, integer, float.

Value 2 can be one of the following types: big integer, double, integer, float.

Returned value type will be double.

Function	Result
hypot(2,4)	4.47213595499958

LeastSignificantBit(value1)

Method is used to return the least significant 64 bits of this UUID's 128 bit value.

Value 1 can be one of the following types: integer.

Returned value will be same as the input argument.

Function	Result
LeastSignificantBit(2)	1.0

log(value1, value2)

Logarithm(base, arg)

Value 1 can be one of the following types: big integer, double, integer, float.

Value 2 can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
log(20,3)	0.3667257913420846

log1(value1)

Function returns the natural logarithm of a number.

Value 1 can be one of the following types: double, integer, float.

Returned value will be of type double.

Function	Result
log1(20)	2.995732273553991

log10(value1)

Logarithm(10, arg)

Value 1 can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
log10(20)	1.301029995663981

log2(value1)

Logarithm(2, arg)

Value 1 can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
log2(20)	4.321928094887362

logFactorial(value1)

Function returns the natural logarithm (base e) of the factorial of its integer argument as a double.

Value 1 can be one of the following types: double, integer, float.

Returned value will be of type double.

Function	Result
logFactorial(20)	42.335616460753485

long()

Converts the input argument value to long. The input argument can be one of the following types: big integer, integer, text, float, timestamp. Returned value type will be big integer.

Function	Result
long(5039505078907524)	5039505078907524
long(22)	22

longFactorial(value1)

Function returns the natural logarithm (base e) of the factorial of its integer argument as a double.

Value 1 can be one of the following types: double, integer, float.

Returned value will be of type double.

Function	Result
longFactorial(10)	15.104412573075516

minimum(value1, value2)

Returns the minimum of two arguments. The first argument is a value to compare with the second argument’s value and can be any one of the following data type: big integer, double, interval, integer, float. The second argument is a value to compare with the first argument’s value and can be any one of the following data type: big integer, double, interval, integer, float.

Examples

Function	Result
minimum(16324, 16321)	16321
minimum(3.16, 3.10)	3.10

Note:

If the user provides two different data types as arguments, then Stream Analytics does implicit conversion to convert one argument to the other argument’s type.

mod(value1, value2)

Functions returns Modulo of a number

Value 1 can be one of the following types: big integer, double, integer, float.

Value 2 can be one of the following types: big integer, double, integer, float.

Returned value will be of the same type as the first argument.

Function	Result
mod(10,3)	1.0

mostSignificantBit(value1)

Function returns the most significant 64 bits of this UUID's 128 bit value .

Value 1 can be one of the following types: integer.

Returned value will be of the same type as the first argument.

Function	Result
mostSignificantBit(10)	3.0

nextAfter(value1, value2)

Function returns the floating-point number adjacent to the first argument in the direction of the second argument.

Value 1 can be one of the following types: double, float.

Value 2 can be one of the following types: double, float.

Returned value will be the same type as the first argument.

Function	Result
nextAfter()

nextDown(value1, value2)

Function returns the floating-point number adjacent to the first argument in the direction of the second argument.

Value 1 can be one of the following types: double, float.

Value 2 can be one of the following types: double, float.

Returned value will be the same type as the first argument.

Function	Result
nextDown()

nextUp(value1)

Function returns the floating-point number adjacent to the first argument in the direction of the second argument.

Value 1 can be one of the following types: double, float.

Returned value will be the same type as the first argument.

Function	Result
nextUp()

pow(value1, value2)

Power function returns m raised to the nth power.

Value 1 can be one of the following types: double, integer, float.

Value 2 can be one of the following types: double, integer, float.

Returned value will be of type double.

Function	Result
pow(12,2)	144

rint(value1)

Returns the double value that is closest in value to the argument and is equal to a mathematical integer.

Value can be one of the following types: double.

Returned value will be of type double.

Function	Result
rint()

round(value1)

Rounds the argument value to the nearest integer value. The input argument can be of the following data types: big integer, double, integer, float.

Examples

Function	Result
round(7.16)	7
round(38.941)	39
round(3.5)	4

scalb(

Function Return d × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set.

Value 1 can be one of the following types: double, float.

Value 2 can be one of the following types: integer.

Returned value will be the same type as the first argument.

Function	Result
scalb(10.0,2)	40.0

signum(value1)

Signum of an argument as a double value.

Value 1 can be one of the following types: number, big integer, double, integer, float.

Returned value will be of type integer.

Function	Result
signum(10)	1.0

sin(value1)

Returns the sine of the input value.

Value 1 can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
sin(7740.8)	0.08419864005868474

sinh(value1)

Returns the Sine hyperbolic of a value.

Value 1 can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
sinh(0.5)	0.5210953054937474

sqrt(value1)

Returns the Square root of the input value.

Value 1 can be one of the following types: big integer, double, integer, float.

Returned value will be of the type double.

Function	Result
sqrt(7434.73)	86.22488040003303

stirlingCorrection(value1)

Returns the correction term of the Stirling approximation of the natural logarithm (base e) of the factorial of the integer argument as a double: STIRLINGCORRECTION

Value 1 can be one of the following types: integer.

Returned value will be of the type double.

Function	Result
stirlingCorrection(70)	0.0011904680924708464

tan(value1)

Returns the Tangent of a value.

Value 1 can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
tan(60)	0.320040389379563

tanh(value1)

Returns the Tangent hyperbolic of a value.

Value 1 can be one of the following types: big integer, double, integer, float.

Returned value will be of type double.

Function	Result
tanh(1)	0.7615941559557649

toDegrees(value1)

Converts the argument value to degrees. The input argument is an angle in radians and can be of type double. The returned value will be the measurement of the angle in degrees and is of type double.

Examples

Function	Result
toDegrees(3.14)	180.0
toDegrees(0.785)	45.0

toRadians(value1)

Converts the argument value to radians. The input argument is an angle in degrees and can be of type double. The returned value will be the measurement of the angle in radians and is of type double.

Examples

Function	Result
toRadians(180.0)	3.14
toRadians(45.0)	0.785

ulp(value1)

Returns the returns the size of an ulp of the argument: ULP.

The input arguments can be one of the following data types: double, float.

Returned value type will be the same as the input value type.

Function	Result
ulp(1451.54)	2.2737367544323206E-13

Using Null-related Functions

The following null-related functions are supported in this release:

Function Name	Description
nvl(value1,value2)	Replaces null with a value of the same type

nvl(value1, value2)

nvl lets you replace null (returned as a blank) with a value of the same type as the first argument. For example, in a list of employees and commission, you can substitute Not Applicable if the employee receives no commission using the nvl(value1,value2) function as nvl(Not Applicable,Commission).

Example

Function	Result
nvl(Not Applicable,Commission)	Not Applicable

Using Statistical Functions

Statistical functions help you in calculating the statistics of different values.

The following statistical functions are supported in this release:

Function Name	Description
beta1(value1,value2,value3)	Returns the area from zero to value3 under the beta density function
betaComplemented(value1,value2,value3)	Returns the area under the right hand tail (from value3 to infinity) of the beta density function
binomial2(value1,value2,value3)	Returns the sum of the terms 0 through value1 of the Binomial probability density. All arguments must be positive.
binomialComplemented(value1,value2,value3)	Returns the sum of the terms value1+1 through value2 of the binomial probability density. All arguments must be positive.
chiSquare(value1,value2)	Returns the area under the left hand tail (from 0 to value2) of the chi square probability density function with value1 degrees of freedom. The arguments must both be positive.
chiSquareComplemented(value1,value2)	Returns the area under the right hand tail (from value2 to infinity) of the chi square probability density function with value1 degrees of freedom. The arguments must both be positive.
errorFunction(value1)	Returns the error function of the normal distribution
errorFunctionComplemented(value1)	Returns the complementary error function of the normal distribution
gamma(value1,value2,value3)	Returns the gamma function of the arguments
gammaComplemented(value1,value2,value3)	Returns the integral from value3 to infinity of the gamma probability density function
incompleteBeta(value1,value2,value3)	Returns the incomplete beta function evaluated from zero to value3
incompleteGamma(value1,value2)	Returns the incomplete gamma function
incompleteGammaComplement(value1,value2)	Returns the complemented incomplete gamma function
logGamma(value1)	Returns the natural logarithm of the gamma function
negativeBinomial(value1,value2,value3)	Returns the sum of the terms 0 through value1 of the negative binomial distribution. All arguments must be positive.
negativeBinomialComplemented(value1,value2,value3)	Returns the sum of the terms value1+1 to infinity of the negative binomial distribution. All arguments must be positive.
normal(value1,value2,value3)	Returns the area under the normal (Gaussian) probability density function, integrated from minus infinity to value1 (assumes mean is zero, variance is one)
normalInverse(value1)	Returns the value for which the area under the normal (Gaussian) probability density function is equal to the argument value1 (assumes mean is zero, variance is one)
poisson(value1,value2)	Returns the sum of the first value1 terms of the Poisson distribution. The arguments must both be positive.
poissonComplemented(value1,value2)	Returns the sum of the terms value1+1 to infinity of the poisson distribution
studentT(value1,value2)	Returns the integral from minus infinity to value2 of the Student-t distribution with value1 > 0 degrees of freedom
studentTInverse(value1,value2)	Returns the value, for which the area under the Student-t probability density function is equal to 1-value1/2. The function uses the studentT function to determine the return value iteratively.

beta1(value1, value2, value3)

Returns the area from zero to value3 under the beta density function.

The input arguments can be one of the following data types: double, float. Returned value is of type double.

Values 1 and 2 must be greater than 0.0. Value 3 must be greater than 0 and less than 1.

Function	Result
beta1(0.1, 1.1, 0.2)	0.8620112116492348
beta1(316.13, 316.13, 0.2)	1.40801423421089E-63

betacomplemented(value1, value2, value3)

Returns the area under the right hand tail (value 3 to infinity) of the beta density function.

The input arguments can be one of the following data types: double, float. Returned value is of type double.

Values 1 and 2 must be greater than 0.0. Value 3 must be greater than 0 and less than 1.

Function	Result
betacomplemented(0.1, 1.1, 0.2)	0.017407170120127144

binomial2(value1, value2, value3)

Returns the sum of the terms 0 through value1 of the Binomial probability density.

The input arguments can be one of the following data types:

• Value 1 - The end term. Data Type: Integer.
• Value 2 - The number of trials. Data type: Integer.
• Value 3 - The probability of success. Value must be between 0.0 and 1.0. Data type: Double, float.

Function	Result
beta1(2, 2, 0.5)	1.0

binomialcomplemented(value1, value2, value3)

Returns the sum of the terms value1 + 1 through value 2, of the Binomial probability density.

The input arguments can be one of the following data types:

• Value 1 - The end term. Data Type: Integer.
• Value 2 - The number of trials. Data type: Integer.
• Value 3 - The probability of success. Value must be between 0.0 and 1.0. Data type: Double, float.

The returned value is of the type double.

Function	Result
binomialcomplemented(2, 3, 0.5)	0.125

chiSquare(value1, value2)

Returns the area under the left hand tail (from 0 to value2) of the Chi square probability density function with value1 degrees of freedom. The arguments must both be positive.

Value 1: The degrees of freedom. Value can be one of the following types: double, float.

Value 2: The integration end point. Value can be one of the following types: double, float.

Returned value type will be double.

Function	Result
chiSquare(3.0, 5.0)	0.8282028557032665

chiSquareComplemented(value1, value2)

Returns the area under the right hand tail (from value2 to infinity) of the Chi square probability density function with value1 degrees of freedom. The arguments must both be positive.

Value 1 is the degrees of freedom. Value can be one of the following types: double, float.

Value 2 is the Chi-square variable. Value can be one of the following types: double, float.

Returned value type will be double.

Function	Result
chiSquareComplemented(value1, value2)	0.1717971442967335

errorFunction(value1)

Returns the error function of the normal distribution.

Value 1 can be one of the following types: double, float.

Returned value type will be double.

Function	Result
errorFunction(5.0)	0.9999999999984626

errorFunctionComplemented(value1)

Returns the complementary Error function of the normal distribution.

Value 1 can be one of the following types: double, float.

Returned value type will be double.

Function	Result
errorFunctionComplemented(5.0)	1.5374597944280347E-12

gamma(value1, value2, value3)

Returns the gamma function of the input arguments.

Value1: The paramater a (alpha) of the gamma distribution. Value can be one of the following types: double, float. Required.

Value 2: The paramater b (beta, lambda) of the gamma distribution. Value can be one of the following types: double, float. Optional.

Value 3: The integration end point. Value can be one of the following types: double, float. Optional.

Returned value type will be double.

Function	Result
gamma(1.0,2.0,5.0)	0.04042768199451279

gammacomplemented(value1, value2, value3)

Returns the integral from value3 to infinity of the gamma probability density function.

Value1: The paramater a (alpha) of the gamma distribution. Value can be one of the following types: double, float.

Value 2: The paramater b (beta, lambda) of the gamma distribution. Value can be one of the following types: double, float. R

value3The integration end point. Value can be one of the following types: double, float.

Returned value type will be double.

Function	Result
gammacomplemented(1.0, 2.0, 5.0)	0.04042768199451279

incompleteBeta(value1, value2, value3)

Returns the Incomplete Beta Function evaluated from zero to value3. Where values must be in range (Value1 && Value2 > 0.0) and (Value3 > 0 && Value3 < 1).

Value1: The alpha parameter of the beta distribution. Value can be one of the following types: double, float. Required.

Value2: The beta parameter of the beta distribution. Value can be one of the following types: double, float. Required.

Value3: The integration end point. Value can be one of the following types: double, float. Required.

Returned value type will be double.

Function	Result
incompleteBeta(1.0,2.0,0.5)	0.75

incompleteGamma(value1, value2)

Returns the Incomplete Gamma function.

Value1: The parameter of the gamma distribution. Value can be one of the following types: double, float. Required.

Value2: The integration end point. Value can be one of the following types: double, float. Required.

Returned value type will be double.

Function	Result
incompleteGamma(1.0,2.0)	0.8646647167633873

incompleteGammaComplement(value1, value2)

Returns the Complemented Incomplete Gamma function.

Value1: The parameter of the gamma distribution. Value can be one of the following types: double, float. Required.

Value2: The integration start point. Value can be one of the following types: double, float. Required.

Returned value type will be double.

Function	Result
incompleteGammaComplement(1.0, 2.0)	0.1353352832366127

logGamma(value1)

Returns the natural logarithm of the gamma function

Value can be one of the following types: double, float.

Returned value will be of type double.

Function	Result
logGamma(7795.6)	62059.66356433673

negativeBinomial(value1, value2, value3)

Returns the sum of the terms 0 through value1 of the Negative Binomial Distribution. All arguments must be positive.

Value1: The end term. Value can be one of the following types: integer. Required.

Value2: The number of trials. Value can be one of the following types: integer. Required.

Value3: The probability of success [must be in (0.0,1.0)]. Value can be one of the following types: double, float. Required.

Returned value type will be double.

Function	Result
negativeBinomial(1,2,0.5)	0.5

negativeBinomialComplemented(value1, value2, value3)

Returns the sum of the terms value1+1 to infinity of the Negative Binomial distribution. All arguments must be positive.

Value1: The end term. Value can be one of the following types: integer. Required.

Value2: The number of trials. Value can be one of the following types: integer. Required.

Value3: The probability of success [must be in (0.0,1.0)]. Value can be one of the following types: double, float. Required.

Returned value type will be double.

Function	Result
negativeBinomialComplemented(1.0, 2.0, 0.5)	0.5

normal(value1, value2, value3)

Returns the area under the Normal (Gaussian) probability density function, integrated from minus infinity to value1 (assumes mean is zero, variance is one).

Value1: The mean of the normal distribution. Value can be one of the following types: double, float. Required.

Value2: The variance of the normal distribution. Value can be one of the following types: double, float. Optional.

Value3: The integration limit. Value can be one of the following types: double, float. Optional.

Returned value type will be double.

Function	Result
normal(5.0,3.0,0.5)	0.004687384229717484

normalInverse(value1)

Returns the value for which the area under the Normal (Gaussian) probability density function is equal to the argument value1 (assumes mean is zero, variance is one).

Input value should be between 0 and 1. The value can be one of the following types: double, float. Required.

Returned value type will be double.

Function	Result
normalInverse(0.5)	0.0

poisson(value1, value2)

Returns the sum of the first value1 terms of the Poisson distribution. Both the arguments must be positive.

Value1: The number of terms. Value can be one of the following types: integer. Required.

Value2: The mean of the poisson distribution. Value can be one of the following types: double, float. Required.

Returned value type will be double.

Function	Result
poisson(61,123.75)	3.0509714140892473E-10

poissonComplemented(value1, value2)

Returns the sum of the terms value1+1 to Infinity of the Poisson distribution.

Value1: The start term. Value can be one of the following types: integer. Required.

Value2: The mean of the poisson distribution. Value can be one of the following types: double, float. Required.

Returned value type will be double.

Function	Result
poissonComplemented(5,3.0)	0.08391794203130347

studentT(value1, value2)

Returns the integral from minus infinity to 'value2' of the Student-t distribution with value1 > 0 degrees of freedom.

Value1: The degrees of freedom. Value can be one of the following types: double, float. Required.

Value2: The integration end point. Value can be one of the following types: double, float. Required.

Returned value type will be double.

Function	Result
studentT(2.0,5.0)	0.9811252243246882

studentTInverse(value1, value2)

Returns the value, for which the area under the Student-t probability density function is equal to 1-value1/2. The function uses the studentT function to determine the return value iteratively.

Value1: The probability. Value can be one of the following types: double, float. Required.

Value2: The size of data set. Value can be one of the following types: integer. Required.

Returned value type will be double.

Function	Result
studentTInverse(0.5, 10)	0.6998121397488263

Using String Functions

The following String functions are supported in this release:

Function Name	Description
coalesce(value1,...)	Returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null
concat(value1,...)	Returns concatenation of values converted to strings
indexof(string,match)	Returns first index of \'match\' in \'string\'or 1 if not found
initcap(value1)	Returns a specified text expression, with the first letter of each word in uppercase and all other letters in lowercase
length(value1)	Returns the length of the specified string
like(value1,value2)	Returns a matching pattern
lower(value1)	Converts the given string to lower case
lpad(value1,value2,value3)	Pads the left side of a string with a specific set of characters (when string1 is not null)
ltrim(value1,value2)	Removes all specified characters from the left hand side of a string
replace(string,match,replacement)	Replaces all \'match\' with \'replacement\' in \'string\'
rpad(value1,value2,value3)	Pads the right side of a string with a specific set of characters (when string1 is not null)
rtrim(value1,value2)	Removes all specified characters from the right hand side of a string
substr(string,from)	Returns substring of a 'string' when indices are between 'from' (inclusive) and up to the end of the string
substring(string,from,to)	Returns substring of a \'string\' when indices are between \'from\' (inclusive) and \'to\' (exclusive)
translate(value1,value2,value3)	Replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time.
upper(value1)	Converts given string to uppercase

coalesce(value1,... )

coalesce returns the first non-null expression in the list of expressions. You must specify at least two expressions. If all expressions evaluate to null then the coalesce function will return null.

For example:

In coalesce(expr1,expr2):

• If expr1 is not null then the function returns expr1.

• If expr1 is null then the function returns expr2.

• If expr1 and expr2 are null then the function returns null.

In coalesce(expr1,expr2,......,exprn)

• If expr1 is not null then the function returns expr1.

• If expr1 is null then the function returns expr2.

• If expr1 and expr2 are null then the function returns the next non-null expression.

Concat(value1,...)

Concat(value1,...) - Concatenation of values converted to strings

Value1: A part of string to concatenate with others. Value can be one of the following types: big integer, number, double, text, integer, float, timestamp. Required.

Vararg1: A part of string to concatenate with others. Value can be one of the following types: big integer, number, double, text, integer, float, timestamp. Optional.

Returned value will be of type text.

Function	Result
Concat(client_name, card_number)	Declan BENNETT0142354466948788

indexof(value1, value2)

Returns first index of 'match' in 'string' or -1 if not found

Value1: First argument. Value can be one of the following types: text. Required.

Value2: Second argument. Value can be one of the following types: text. Required.

Returned value type will be integer.

Function	Result
indexof(client_name,"c"), where client name is Alphonse Gabriel Capone	17
indexof(client_name,"c"), where client name is Braden Gray	-1

initcap(value1)

Function returns a specified text expression, with the first letter of each word in uppercase and all other letters in lowercase : INITCAP

Value1: A text expression. Value can be one of the following types: text.

Returned value will be of type text.

Function	Result
initcap(client_name), where client name is Owen TAYLOR	Owen Taylor

length(value1)

Returns the length in characters of the string passed as an input argument. The input argument is of the data type text. The returned value is an integer representing the total length of the string.

If value1 is null, then length(value1) returns null.

If value1 is an empty string, then length(value1) returns null.

Examples

Function	Result
length(“one”)	3
length()	ERROR: Function has invalid parameters.
length(“john”)	4
length(” “)	NULL
length(null)	NULL
length(“[e-mail]”)	30

like(string, pattern)

Function returns 'true' or 'false' based on the string matching the supplied pattern. .

Value 1 can be one of the following types: text.

Value 2 can be one of the following types: text.

Returned value type will be boolean.

Function	Result
like(client_name, "ADAMS"), where client name is Cameron Adams	True
like(client_name, "ADAMS"), where client name is Levi Gray	False

lower(value1)

Converts a string to all lower-case characters. The input argument is of the data type text. The returned value is the lowercase of the specified string.

Examples

Function	Result
lower(“PRODUCT”)	product
lower(“ABCdef”)	abcdef
lower(“abc”)	abc

lpad(value1, value2, value3)

LPad(text-exp , length [,pad-exp]) - Function pads the left-side of a string with a specific set of characters (when string1 is not null). : LPAD

Value1: text-exp - A text expression that you want to pad. Value can be one of the following types: text.

Value 2: length - The total length of the return value as it is displayed on your screen. Value can be one of the following types: integer.

Value 3: pad-exp - A text expression that specifies the padding characters. The default value of pad-exp is a single blank. Value can be one of the following types: text.

Returned value type will be text.

Function	Result
lpad("David",10,"e")	eeeeeDavid

ltrim(value1, value2)

The ltrim() function removes all specified characters from the left-hand side of a string : LTRIM.

Value 1 can be one of the following types: text.

Value 2 can be one of the following types: text.

Returned value type will be text.

Function	Result
ltrim(client_name, "A"), where client_name is Alphonse Gabriel CAPONE	lphonse Gabriel CAPONE

replace(string, match, replacement)

Replaces all match characters in a string with replacement characters. The first input argument is the string and is of the data type text. The second argument is the match and is of the data type text. The third argument is replacement and is of data type text. The returned value is a text in which the third string argument (replacement) replaces the second string argument (match).

If match is not found in the string, then the original string will be returned.

Examples

Function	Result
replace(“aabbccdd”,”cc”,”ff”)	aabbffdd
replace(“aabbcccdd”,”cc”,”ff”)	aabbffcdd
replace(“aabbddee”,”cc”,”ff”)	aabbddee

rpad(value1, value2, value3)

RPad(text-exp , length [,pad-exp]) - Function pads the right-side of a string with a specific set of characters (when string1 is not null). : RPAD

Value 1: text-exp - A text expression that you want to pad. Value can be one of the following types: text.

Value 2: length - The total length of the return value as it is displayed on your screen. Value can be one of the following types: integer.

Value 3: pad-exp - A text expression that specifies the padding characters. The default value of pad-exp is a single blank. Value can be one of the following types: text.

Returned value will be of type text.

Function	Result
rpad("Levi Cruz", 25, "a")	Levi Cruzaaaaaaaaaaaaaaaa

rtrim(value1, value2)

The rtrim() function removes all specified characters from the right-hand side of a string : RTRIM.

Value 1 can be one of the following types: text.

Value 2 can be one of the following types: text.

Returned value type will be text.

Function	Result
rtrim(client_name, "S"), where client_name is Cooper DAVIS	Cooper DAVI

substr()

Substr(string, from) - Substring of a 'string' when indices are between 'from' (inclusive) and up to the end of the string.

Value 1 can be one of the following types: text.

Value 2 can be one of the following types: integer.

Returned value type will be text.

Function	Result
substr(client_name, 4),where client_name is Logan THOMPSON	n THOMPSON

substring(string, from, to)

Returns a substring of a string when indices are between from (inclusive) and to (exclusive). The first input argument is the string and is of the data type text. The second argument is the start index and is an integer. The third argument is the finish index and is an integer. The returned value is a substring and is of type text.

Examples

Function	Result
substring(“abcdefgh”,3,7)	cdef
substring(“abcdefgh”,1,6)	abcde

translate(expression, from_string, to_string)

Function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time.

Vallue1: exp - A text expression in which you want to replace characters. Value can be one of the following types: text.

Value2: from_string - A text expression that is the characters you want to replace. Value can be one of the following types: text.

Value3: to_string - A text expression that is the characters that you want to use for replacement in the order of from_string. When you include fewer characters in this argument than are in from_string, the function removes the extra characters in from_string from the return value. Value can be one of the following types: text.

Returned value type will be text.

Function	Result
translate(client_name, "JONES", "Mark"), where the value for client_name is Cooper JONES.	Cooper Mark

upper(value1)

Converts a string to all upper-case characters. The input argument is of the data type text. The returned value is the uppercase of the specified string.

Examples

Function	Result
upper(“name”)	NAME
upper(“abcdEFGH”)	ABCDEFGH
upper(“ABCD”)	ABCD