Function

Description

Usage

Where Clause Usage

Example

Result

REPLACE

Replaces all occurrences of a specified string value with another string value.

In the given example all occurrences of 'cd' string is replaced by 'XX' in string 'abcdefabcdef'.

@@REPLACE ( s1, s2, r )

replace ( s1, s2, r )

replace( 'abcdefabcdef', 'cd', 'XX')

abXXefabXXef

LOWER

Returns a character expression after converting uppercase character data to lowercase.

In the given example 'TOM' is converted to lower case.

@@LOWER ( s )

lower ( s )

lower('TOM')

tom

UPPER

Returns a character expression with lowercase character data converted to uppercase.

In the given example 'tom' is converted to uppercase.

@@UPPER (s

upper ( s )

upper('tom')

TOM

CHAR

Converts an int ASCII code to a character.

In the given example  int ASCII code 65 is converted to Character i.e 'A'

@@CHR( s )

chr( s )

chr(65)

A

ASCII

Returns the ASCII code value of the leftmost character of a character expression.

In the given example ASCII code of character 'x' is returned i.e 120

@@ASCII( n )

ascii( n )

ascii('x')

120

SUBSTR

Returns part of a character, binary, text, or image expression

In the given example 2 characters from 3rd position is returned from the string 'alphabet' i.e ph

@@SUBSTR ( s1, p, l )

substr ( s1, p, l )

substr('alphabet', 3, 2)

ph

CONCAT

Returns a string that is the result of concatenating two or more string values.

In the given example strings 'abcde', 2, NULL, 22 are concatnated  i.e abcde222

@@CONCAT ( s1, s2 )

concat ( s1, [s2,[…]] )

concat('abcde', 2, NULL, 22)

abcde222

RPAD

Returns a string of the given length by adding the second string to the first string repeatedly

In the given example string 'xy' is added to string 'hi' repeatedly forming length of 5.

@@RPAD ( s1, n, s2)

rpad ( s1, n, s2)

rpad('hi', 5, 'xy')

hixyx

TRIM

Removes the longest string containing only the specified characters (default is blank) from the beginning and end of string, or both.

@@TRIM ( s, r )

trim ( s, r )

trim( 'xxxTomxxx', 'x')

OR

trim('[UIPart Name/Identifier]', 'x')

Tom

LTRIM

Returns a character string after truncating all leading blanks.

@@LTRIM ( s, r )

ltrim ( s, r )

ltrim('xxxtrimxxx', 'x')

trimxxx

RTRIM

Returns a character string after truncating all trailing blanks..

@@RTRIM ( s, r )

rtrim ( s, r )

rtrim('xxxtrimxxxx', 'x')

xxxtrim

LEFT

Returns the left part of a character string with the specified number of characters.

@@LEFT (s, n)

LEFT (s, n)

left('appexe','3')

app

RIGHT

Returns the right part of a character string with the specified number of characters.

@@RIGHT (s, n)

RIGHT (s, n)

right('appexe','3')

exe

Function

Usage

Description

Return type

YEAR

@@YEAR( date )

Returns year value from date or datetime data

int

MONTH

@@MONTH( date )

Returns month value from date or datetime data

int

DAY

@@DAY( date )

Returns day value from date or datetime data

int

HOUR

@@HOUR (datetime)

Returns hour value from time or datetime data

int

MINUTE

@@MINUTE (datetime)

Returns minute value from time or datetime data

int

SECOND

@@SECOND (datetime)

Returns second value from time or datetime data

int

DATE_DIFF

@@DATE_DIFF('datepart','startdate','enddate')

Returns Time or Date difference between startdate and enddate . Here the "datepart" defines the unit: 'time', 'seconds', 'minutes'. 'hours', 'days', 'months', 'years'

int

PREV_DATE

@@PREV_DATE('date')

Returns the previous date to the given date

Date should follow below formats :

'yyyy-mm-dd'

'yyyy-mm-dd hh:mm:ss'

'__NOW__'

date

NEXT_DATE

@@NEXT_DATE('date')

Returns the next date to the given date

Date should follow below formats :

'yyyy-mm-dd'

'yyyy-mm-dd hh:mm:ss'

'__NOW__'

date

DOW

@@DOW(datetime)

Returns day of week(0:Sun - 6:Sat) from time or datetime data. Returns blank if no date is specified.

text

WOM

@@WOM(datetime)

Returns week of month(1-6) from time or datetime data.

Returns blank if no date is specified.

text

WEEK

@@WEEK(__NOW__)

Returns the week of the year.

int