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
Required: YestextLOWER
Required: YestextPROPER
Required: YestextTRIM
Required: YestextLEN
Required: YestextLEFT
Required: Yestext
Required: NocountRIGHT
Required: Yestext
Required: NocountMID
Required: Yestext
Required: Yesstart
Required: YescountCONCAT
Required: YestextFIND
Required: Yesfind
Required: Yeswithin
Required: NostartSEARCH
Required: Yesfind
Required: Yeswithin
Required: NostartSUBSTITUTE
Required: Yestext
Required: Yesold
Required: Yesnew
Required: NoinstanceREPLACE
Required: Yestext
Required: Yesstart
Required: Yescount
Required: YesnewREPT
Required: Yestext
Required: YestimesEXACT
Required: Yestext1
Required: Yestext2TEXTBEFORE
Required: Yestext
Required: Yesdelimiter
Required: Noinstance
Required: Nomatch_modeTEXTAFTER
Required: Yestext
Required: Yesdelimiter
Required: Noinstance
Required: Nomatch_modeTEXTSPLIT
Required: Yestext
Required: Yesdelimiter
Required: Norow_delimiter
Required: Noignore_empty
Required: Nopad_withCHAR
Required: YescodeCODE
Required: YestextASC
Required: YestextCLEAN
Required: YestextCONCATENATE
Required: YestextLENB
Required: YestextLEFTB
Required: Yestext
Required: NobytesRIGHTB
Required: Yestext
Required: NocountMIDB
Required: Yestext
Required: Yesstart
Required: YescountREPLACEB
Required: Yestext
Required: Yesstart
Required: Yescount
Required: YesnewT
Required: YesvalueFINDB
Required: Yesfind
Required: Yeswithin
Required: NostartREPLACEALL
Required: Yestext
Required: Yesold
Required: Yesnew
Required: NoinstanceISNULL
Required: Yesvalue
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
UPPER | text | The text in capitals | UPPER("abc") |
LOWER | text | The text in lower case | LOWER("ABC") |
PROPER | text | Each word starts with a capital | PROPER("hello world") |
TRIM | text | Removes spaces at the ends and extra spaces between words | TRIM(" a b ") |
LEN | whole number | The number of characters | LEN("hello") |
LEFT | text | The first characters | LEFT("hello", 2) |
RIGHT | text | The last characters | RIGHT("hello", 2) |
MID | text | Characters from the middle | MID("hello", 2, 3) |
CONCAT | text | The pieces joined | CONCAT("a", "b", "c") |
FIND | whole number | The position of the first match, counting from 1, matching case | FIND("l", "hello") |
SEARCH | whole number | The position of the first match, ignoring case | SEARCH("L", "hello") |
SUBSTITUTE | text | Replaces each match | SUBSTITUTE("a-b-c", "-", "+") |
REPLACE | text | Replaces characters by position | REPLACE("hello", 1, 1, "J") |
REPT | text | The text repeated | REPT("ab", 3) |
EXACT | true or false | True when the two are identical, matching case | EXACT("a", "A") |
TEXTBEFORE | text | The text before the delimiter | TEXTBEFORE("a-b-c", "-") |
TEXTAFTER | text | The text after the delimiter | TEXTAFTER("a-b-c", "-") |
TEXTSPLIT | list of text | The pieces | TEXTSPLIT("a-b-c", "-") |
CHAR | text | The character for the code | CHAR(65) |
CODE | whole number | The code of the first character | CODE("A") |
ASC | text | Full width letters, digits and punctuation changed to their normal width, and the full width space to a space | ASC("AB12") |
CLEAN | text | The text without tabs, line breaks and other control characters | CLEAN("a\tb") |
CONCATENATE | text | The pieces joined. It works exactly as CONCAT does | CONCATENATE("a", "b") |
LENB | whole number | The number of bytes the text takes. A letter with an accent takes two | LENB("café") |
LEFTB | text | The first bytes of the text. Cutting inside a character is refused with HTQL-8003 | LEFTB("café", 3) |
RIGHTB | text | The last characters. It counts characters, as RIGHT does | RIGHTB("café", 2) |
MIDB | text | Characters from the middle. It works exactly as MID does | MIDB("ABC-1042", 5, 4) |
REPLACEB | text | Replaces characters by position. It works exactly as REPLACE does | REPLACEB("ABC-1042", 1, 3, "XYZ") |
T | text | The text itself when the value is text, and empty text for a number or anything else | T("Perth") |
FINDB | whole number | The position of the first match counted in bytes, matching case. A start inside a character, or no match, is refused with HTQL-8003 | FINDB("au", "café au lait") |
REPLACEALL | text | Replaces each match. It works exactly as SUBSTITUTE does | REPLACEALL("a-b-c", "-", "+") |
ISNULL | true or false | True when the value is null, or is a column the row does not hold | ISNULL(null) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-5005 | A text function was given a number, such as LEN(12345): it expects a text value | Pass text in quotes |
HTQL-8001 | TEXTBEFORE 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-8003 | FIND 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,LEFTBandFINDBcount 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")andSUBSTITUTE("a_b", "_", " ")both give"ab".CONCATalso leaves out a number argument; pass the number as text. TEXTBEFOREandTEXTAFTERcount the delimiter's occurrences from 1, or from the end when the number is negative, andmatch_modeistrueto match case orfalseto 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.REPLACEanswersHTQL-5005forREPLACE(card, 1, 12, REPT("*", 12)).EXACT(UPPER("abc"), "ABC")andFIND("a", TRIM(x))answer "index out of bounds: the len is 1 but the index is 1", andSUBSTITUTE(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 ofLEN,LEFT,UPPER,PROPER,MIDorSUBSTITUTEworks, as the examples show. ForCONCAT, 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.