TIBCO EBX®
Documentation > Developer Guide > SQL in EBX®
Navigation modeDocumentation > Developer Guide > SQL in EBX®

String operators and functions

The table below lists all the SQL string operators and functions supported by EBX®, along with their standard SQL syntax. Some functions may have optional parameters: they are surrounded by square brackets.

Operator syntax

Description and example(s)

string || string

Concatenates two character strings

SELECT 'Hello ' || 'world !' :  Hello world !

CHAR_LENGTH(string)

Returns the number of characters in a character string

SELECT CHAR_LENGTH('Alfreds Futterkiste') :  19

CHARACTER_LENGTH(string)

As CHAR_LENGTH(string)

SELECT CHARACTER_LENGTH('Alfreds Futterkiste') :  19

UPPER(string)

Returns a character string converted to upper case

SELECT UPPER('SQL Tutorial is FUN!') :  SQL TUTORIAL IS FUN!

POSITION(string1 IN string2 [FROM integer])

Returns the position of the first occurrence of string1 in string2 starting at a given point if specified

SELECT POSITION('is fun' IN 'Tutorial is FUN!') :  0

SELECT POSITION('fun' IN 'Tutorial is FUN, very FUN!' FROM 17) :  0

TRIM( { BOTH | LEADING | TRAILING } character FROM string)

Removes the longest string containing only the character from the start/end/both ends of string

SELECT TRIM(' ' FROM ' #SQL Tutorial! ') :  #SQL Tutorial!

SELECT TRIM(LEADING ' ' FROM ' #SQL Tutorial! ') :  #SQL Tutorial!

SELECT TRIM(TRAILING ' ' FROM ' #SQL Tutorial! ') :  #SQL Tutorial!

SELECT TRIM(BOTH ' ' FROM ' #SQL Tutorial! ') :  #SQL Tutorial!

OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ])

Replaces a substring of string1 with string2

SELECT OVERLAY('Tutorial is very FUN!' PLACING 'VERY' FROM 13) :  Tutorial is VERY FUN!

SELECT OVERLAY('Tutorial is very FUN!' PLACING 'VERY' FROM 13 FOR 4) :  Tutorial is VERY FUN!

SUBSTRING(string FROM integer [FOR integer])

Returns a substring of a character string starting at a given point

SELECT SUBSTRING('Tutorial is very FUN!' FROM 13) :  very FUN!

SELECT SUBSTRING('Tutorial is very FUN!' FROM 13 FOR 4) :  very

Documentation > Developer Guide > SQL in EBX®