Skip to main content

String Functions

The string function package contains functions to manipulate and analyze strings and characters. The string function package is part of the standard SQRL function library.

IMPORT string.*; -- imports all string functions
IMPORT string.concat; -- imports single string function

Reference

The following table lists all the functions in the string package with a short description. The table also includes the name of the function in SQL, which may be slightly different.

Function NameDescriptionSQL Name
asciiThis SQL function returns the ASCII code of the leftmost character of a given string. For example, ascii('A') would return 65.ASCII
chrThis SQL function returns the character based on the number code in its argument. For example, chr(65) returns the character 'A'.CHR
concatThis SQL function returns a concatenated string of two or more strings. For example, concat('Hello', 'World') returns the string 'HelloWorld'.CONCAT_FUNCTION
concatWSThis SQL function returns a string that is the result of concatenating a series of strings separated by a specified separator. For example, concatWS('-', 'Hello', 'World') returns 'Hello-World'.CONCAT_WS
decodeThis SQL function returns a value based on a comparison of an expression to one or more search values. For example, decode(1, 1, 'One', 2, 'Two', 'Other') would return 'One' as the result.DECODE
encodeThis SQL function returns an encoded string of the input string using the specified algorithm. For example, encode('Hello World', 'SHA1') returns '2aae6c35c94fcfb415dbe95f408b9ce91eeENCODE
fromBase64This SQL function returns a string decoded from its base64 representation. For example, fromBase64('SGVsbG8gV29ybGQ=') returns 'Hello World'.FROM_BASE64
initcapThis SQL function returns a string with the first letter of each word capitalized. For example, invoking the function with the string "hello world" would return "Hello World".INITCAP
instrThis SQL function returns the position of a substring within a string. For example, instr('Hello World', 'World') returns 7, indicating that the substring 'World' starts at the 7th character in the string 'Hello World'.INSTR
leftThis SQL function returns a specified number of characters from the left side of a string. For example, left('Hello World', 5) would return 'Hello' as the result.LEFT
lengthThis SQL function returns the number of characters in a given string. For example, length('Hello World') returns 11.CHAR_LENGTH
locateThis SQL function returns the position of a substring within a string. For example, locate('world', 'Hello world') returns 7, indicating that the substring 'world' begins at the 7th character in the string 'Hello world'.LOCATE
lpadThis SQL function returns a string padded on the left with a specified set of characters to a certain length. For example, lpad('Hello', 10, '*') would return 'Hello*'.LPAD
ltrimThis SQL function returns a string with all leading spaces removed. For example, ltrim(' Hello World') would return 'Hello World'.LTRIM
overlayThis SQL function returns a string with a substring replaced by a new substring. For example, overlay('ABCDE' PLACING 'XYZ' FROM 3 FOR 3) returns 'ABXYZE'.OVERLAY
parseURLThis SQL function returns the components of a URL as separate fields. For example, parseURL('https://www.example.com/path/to/page?query=string') would return the protocol ('https'), host ('www.example.com'),PARSE_URL
positionThis SQL function returns the position of a substring within a string. For example, position('world' IN 'hello world') returns 7, indicating that the substring 'world' begins at the 7th character in the string 'hello world'.POSITION
regexpThis SQL function returns a boolean value (true or false) indicating whether a given string matches a given regular expression. For example, regexp('Hello World', '^Hello') returns true.REGEXP
regexpExtractThis SQL function returns a specific part of a string that matches a regular expression pattern. For example, regexpExtract('Hello World', '[a-zA-Z]+', 0) returns 'Hello'.REGEXP_EXTRACT
regexReplaceThis SQL function returns a string with all substrings that match a regular expression pattern replaced by a specified replacement string. For example, regexReplace('Hello World', 'l', 'z') would return 'Hezzo Worzd'.REGEXP_REPLACE
repeatThis SQL function returns a string consisting of the specified string repeated a specified number of times. For example, repeat('Hello', 3) would return 'HelloHelloHello'.REPEAT
replaceThis SQL function returns a new string with some or all occurrences of a specified string replaced with another specified string. For example, replace('Hello World', 'World', 'Universe') would return 'Hello Universe'.REPLACE
reverseThis SQL function returns a character string in reverse order. For example, reverse('Hello World') would return 'dlroW olleH'.REVERSE
rightThis SQL function returns a specified number of characters from the right side of a given string. For example, right('Hello World', 5) would return "World".RIGHT
rpadThis SQL function returns a string that is right-padded with a specific set of characters to a specified length. For example, rpad('Hello', 10, '*') would return 'Hello*'.RPAD
rtrimThis SQL function returns a character string with the specified characters removed from the right side of the string. For example, rtrim('Hello World ', ' ') would return 'Hello World'.RTRIM
splitIndexThis SQL function returns a substring from a given string based on a delimiter and index. For example, splitIndex('Hello,World', ',', 1) would return 'Hello' as the result.SPLIT_INDEX
strToMapThis SQL function returns a map from a string of key-value pairs. For example, strToMap('a=1,b=2,c=3') returns a map with keys 'a', 'b', and 'c' and values 'STR_TO_MAP
substrThis SQL function returns a substring from a given string, starting at a specified position and including a specified number of characters. For example, substr('Hello World', 6, 5) would return 'World'.SUBSTR
substringThis SQL function returns a portion of a string, starting at a specified position and including a specified number of characters. For example, substring('Hello World', 6, 5) would return 'World'.SUBSTRING
toBase64This SQL function returns a base64-encoded string from a given input string. For example, toBase64('Hello World') returns 'SGVsbG8gV29ybGQ=' which is the base64-encoded version ofTO_BASE64
trimThis SQL function returns a string with leading and trailing whitespace removed. For example, trim(' Hello World ') would return 'Hello World'.TRIM
upperThis SQL function returns a string with all characters converted to uppercase. For example, upper('hello world') would return 'HELLO WORLD'.UPPER