Text manipulation functions

Cut, join, find and change text in a statement.

Syntax

Write the function's name and the text, with the positions or parts it asks for.

UPPER(text)
LOWER(text)
PROPER(text)
TRIM(text)
LEN(text)
LEFT(text, [count])
RIGHT(text, [count])
MID(text, start, count)
CONCAT(text)
FIND(find, within, [start])
SEARCH(find, within, [start])
SUBSTITUTE(text, old, new, [instance])
REPLACE(text, start, count, new)
REPT(text, times)
EXACT(text1, text2)
TEXTBEFORE(text, delimiter, [instance], [match_mode])
TEXTAFTER(text, delimiter, [instance], [match_mode])
TEXTSPLIT(
  text,
  delimiter,
  [row_delimiter],
  [ignore_empty],
  [pad_with]
)
CHAR(code)
CODE(text)
ASC(text)
CLEAN(text)
CONCATENATE(text)
LENB(text)
LEFTB(text, [bytes])
RIGHTB(text, [count])
MIDB(text, start, count)
REPLACEB(text, start, count, new)
T(value)
FINDB(find, within, [start])
REPLACEALL(text, old, new, [instance])
ISNULL(value)

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • UPPER

    text

    Required: Yes
  • LOWER

    text

    Required: Yes
  • PROPER

    text

    Required: Yes
  • TRIM

    text

    Required: Yes
  • LEN

    text

    Required: Yes
  • LEFT

    text

    Required: Yes
  • count

    Required: No
  • RIGHT

    text

    Required: Yes
  • count

    Required: No
  • MID

    text

    Required: Yes
  • start

    Required: Yes
  • count

    Required: Yes
  • CONCAT

    text

    Required: Yes
  • FIND

    find

    Required: Yes
  • within

    Required: Yes
  • start

    Required: No
  • SEARCH

    find

    Required: Yes
  • within

    Required: Yes
  • start

    Required: No
  • SUBSTITUTE

    text

    Required: Yes
  • old

    Required: Yes
  • new

    Required: Yes
  • instance

    Required: No
  • REPLACE

    text

    Required: Yes
  • start

    Required: Yes
  • count

    Required: Yes
  • new

    Required: Yes
  • REPT

    text

    Required: Yes
  • times

    Required: Yes
  • EXACT

    text1

    Required: Yes
  • text2

    Required: Yes
  • TEXTBEFORE

    text

    Required: Yes
  • delimiter

    Required: Yes
  • instance

    Required: No
  • match_mode

    Required: No
  • TEXTAFTER

    text

    Required: Yes
  • delimiter

    Required: Yes
  • instance

    Required: No
  • match_mode

    Required: No
  • TEXTSPLIT

    text

    Required: Yes
  • delimiter

    Required: Yes
  • row_delimiter

    Required: No
  • ignore_empty

    Required: No
  • pad_with

    Required: No
  • CHAR

    code

    Required: Yes
  • CODE

    text

    Required: Yes
  • ASC

    text

    Required: Yes
  • CLEAN

    text

    Required: Yes
  • CONCATENATE

    text

    Required: Yes
  • LENB

    text

    Required: Yes
  • LEFTB

    text

    Required: Yes
  • bytes

    Required: No
  • RIGHTB

    text

    Required: Yes
  • count

    Required: No
  • MIDB

    text

    Required: Yes
  • start

    Required: Yes
  • count

    Required: Yes
  • REPLACEB

    text

    Required: Yes
  • start

    Required: Yes
  • count

    Required: Yes
  • new

    Required: Yes
  • T

    value

    Required: Yes
  • FINDB

    find

    Required: Yes
  • within

    Required: Yes
  • start

    Required: No
  • REPLACEALL

    text

    Required: Yes
  • old

    Required: Yes
  • new

    Required: Yes
  • instance

    Required: No
  • ISNULL

    value

    Required: Yes

Examples

A name typed with extra spaces and odd capitals, cleaned for a mailing list.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
UPPERtextThe text in capitalsUPPER("abc")
LOWERtextThe text in lower caseLOWER("ABC")
PROPERtextEach word starts with a capitalPROPER("hello world")
TRIMtextRemoves spaces at the ends and extra spaces between wordsTRIM(" a b ")
LENwhole numberThe number of charactersLEN("hello")
LEFTtextThe first charactersLEFT("hello", 2)
RIGHTtextThe last charactersRIGHT("hello", 2)
MIDtextCharacters from the middleMID("hello", 2, 3)
CONCATtextThe pieces joinedCONCAT("a", "b", "c")
FINDwhole numberThe position of the first match, counting from 1, matching caseFIND("l", "hello")
SEARCHwhole numberThe position of the first match, ignoring caseSEARCH("L", "hello")
SUBSTITUTEtextReplaces each matchSUBSTITUTE("a-b-c", "-", "+")
REPLACEtextReplaces characters by positionREPLACE("hello", 1, 1, "J")
REPTtextThe text repeatedREPT("ab", 3)
EXACTtrue or falseTrue when the two are identical, matching caseEXACT("a", "A")
TEXTBEFOREtextThe text before the delimiterTEXTBEFORE("a-b-c", "-")
TEXTAFTERtextThe text after the delimiterTEXTAFTER("a-b-c", "-")
TEXTSPLITlist of textThe piecesTEXTSPLIT("a-b-c", "-")
CHARtextThe character for the codeCHAR(65)
CODEwhole numberThe code of the first characterCODE("A")
ASCtextFull width letters, digits and punctuation changed to their normal width, and the full width space to a spaceASC("AB12")
CLEANtextThe text without tabs, line breaks and other control charactersCLEAN("a\tb")
CONCATENATEtextThe pieces joined. It works exactly as CONCAT doesCONCATENATE("a", "b")
LENBwhole numberThe number of bytes the text takes. A letter with an accent takes twoLENB("café")
LEFTBtextThe first bytes of the text. Cutting inside a character is refused with HTQL-8003LEFTB("café", 3)
RIGHTBtextThe last characters. It counts characters, as RIGHT doesRIGHTB("café", 2)
MIDBtextCharacters from the middle. It works exactly as MID doesMIDB("ABC-1042", 5, 4)
REPLACEBtextReplaces characters by position. It works exactly as REPLACE doesREPLACEB("ABC-1042", 1, 3, "XYZ")
TtextThe text itself when the value is text, and empty text for a number or anything elseT("Perth")
FINDBwhole numberThe position of the first match counted in bytes, matching case. A start inside a character, or no match, is refused with HTQL-8003FINDB("au", "café au lait")
REPLACEALLtextReplaces each match. It works exactly as SUBSTITUTE doesREPLACEALL("a-b-c", "-", "+")
ISNULLtrue or falseTrue when the value is null, or is a column the row does not holdISNULL(null)
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-5005A text function was given a number, such as LEN(12345): it expects a text valuePass text in quotes
HTQL-8001TEXTBEFORE or TEXTAFTER did not find the delimiter, or the instance asked for is beyond the matches (#N/A)Check the delimiter and the instance
HTQL-8003FIND did not find the text (#VALUE!)Check the text, or use SEARCH for a match that ignores case
Limitations
  • These functions take text. A number such as UPPER(5) is refused: write "5".
  • Positions count from 1 and count characters, so accented text is cut and found where it appears. LENB, LEFTB and FINDB count bytes.
  • Today a text value in quotes loses the spaces at its start and end, so a space cannot be placed between pieces: CONCAT("a ", "b") and SUBSTITUTE("a_b", "_", " ") both give "ab". CONCAT also leaves out a number argument; pass the number as text.
  • TEXTBEFORE and TEXTAFTER count the delimiter's occurrences from 1, or from the end when the number is negative, and match_mode is true to match case or false to ignore it.
  • Coming: some functions do not accept another function's result as an argument. CONCAT(UPPER("a"), "b") answers "b", without the function's part. REPLACE answers HTQL-5005 for REPLACE(card, 1, 12, REPT("*", 12)). EXACT(UPPER("abc"), "ABC") and FIND("a", TRIM(x)) answer "index out of bounds: the len is 1 but the index is 1", and SUBSTITUTE(UPPER(x), "A", "4") answers "index out of bounds: the len is 2 but the index is 2". A function's result as the first argument of LEN, LEFT, UPPER, PROPER, MID or SUBSTITUTE works, as the examples show. For CONCAT, hold the result in a variable first and pass the variable.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Use TRIM on text from outside before you compare it.
  • Use SEARCH when case does not matter, and FIND when it does.