String SQL Functions

This topic covers functions built into the Manifold query engine, called SQL functions or query functions,  that begin with String.   Functions that begin with string manipulate text strings and string data, including conversions and extractions, all fully supporting collations.  Utility functions provide an extensive array of capabilities for manipulating popular text formats like JSON, and to manipulate strings in sophisticated ways using regular expressions.

 

The Manifold query engine also supports declaring and calling functions, including script functions, functions in external files and compiled functions as .NET assemblies.  

 

 

 

Manifold has so many SQL functions they have been grouped into several topics:

 

 

 

 

 

 

 

 

The list of functions below uses the same nomenclature as the function templates in the Query Builder.  SQL functions take arguments as specified in angle < > brackets.  The data type or value type the function returns is given after the : colon.    For example, Chr(<value>) : <string> takes a numeric <value> and returns a text <string>, as noted in the description a string that is a single character.   CoordSystems() : <table> takes no arguments and returns a table.

 

Examples in the list below which use an ? expression evaluation command are intended to be run in a Command Window.   Do not add a semicolon ; character when using the ? command in a Command window.  When using the ? command we use CALL when the function returns more than just a value, for example, when it returns a table.

 

Indexes are always zero based: for example, in an object consisting of three branches the first branch is branch 0, the second is branch 1 and the third is branch 2.

 

This is a long list, so to find a function it is best when viewing this topic in a browser to do a Ctrl-F to open a Find box and to enter the function name of interest to be able to jump to that function.  The list in this topic is comprehensive but new items are often added with updates to Manifold and may appear in the product before they appear in this documentation.   See the list in the query builder tab of the Command Window for an authoritative list of operators, commands and functions.

Constants

See the Identifiers, Constants and Literals topic for some useful constants.   For information on functions that use a <filter> argument or which produce a filter definition matrix, see the How Matrix Filters Work topic.

Functions

Language codes used by functions that take a language argument are standard language codes as used in many settings in Windows, such as de-DE for German language as used in Germany, or en-US for English as used in the US.   A table giving 655 language codes, including broad variations as haw-US for Hawaiian language as used in the United States, can be downloaded in the language_codes.map Release 9 project from the Manifold website.

 

 

StringBinaryBase64(<string) : <value>

Given a base64-encoded string that encodes a binary value, returns the binary value.

StringBinaryHex(<string>) : <value>

Given a hexadecimal-encoded string that encodes a binary value, returns the binary value.  

StringCompareCollate(<string>, <string>, <collate-code>) : <value>

Same as Compare but compares two strings using the specified collation.  

 

Returns -1 if the first string is less than the second string, 1 if the first string is greater than the second string and 0 if the first and second strings are the same.   

 

In the examples that follow 928 is the collation code for en-US (US English) that is case sensitive.   930 is the collation code for en-US where case does not matter.

 

? StringCompareCollate('John','john', 928)

 

returns 1, meaning the first string is greater than the second string.  

 

? StringCompareCollate('John','john', 930)

 

returns 0, meaning the two strings are the same (as they are if case does not matter).

 

Collations are rules that specify how to compare text values.  The CollateCode function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.

StringCompareKeyCollate(<string>, <collate-code>) : <value>

Given a string and a specified collation returns a binary representation of the text using the specified collation.  The produced key can be compared to keys for other strings with the same collation, and such comparisons will yield the same result as if the comparison was done using StringCompareCollate(). However, comparing binary keys is usually much faster than comparing strings using a given collation.

 

Collations are rules that specify how to compare text values.  The CollateCode function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.

StringContains(<string>, <substring>) : <boolean>

Given a string and a substring returns True if the string contains the substring using case-sensitive comparison.

 

? StringContains('Long John Silver', 'john')

 

returns 0 for False,  because when case matters john does not occur in the first string.

 

? StringContains('Long John Silver', 'John')

 

returns 1 for True.

StringContainsCollate(<string>, <substring>, <collate-code>) : <boolean>

Given a string, a substring and a collation returns True if the string contains the substring using the rules of the specified collation.   

 

In the examples that follow 928 is the collation code for en-US (US English) that is case sensitive.   930 is the collation code for en-US where case does not matter.

 

? StringContainsCollate('Long John Silver', 'john', 928)

 

returns 0 for False,  because if case matters john does not occur in the first string.

 

? StringContainsCollate('Long John Silver', 'john', 930)

 

returns 1 for True,  because if case does not matter john occurs as the second word in the first string.

 

Collations are rules that specify how to compare text values.  The CollateCode function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.

StringDateTime(<value>, <lang>): <date>

The function takes a string and a language, and returns a datetime value.   The function attempts to parse the string into a date time value according to the specified language.  No language specification defaults to neutral language.

 

? StringDateTime('March 15, 2021 8:30 PM', '')

 

returns a datetime value: 03/15/2021 20:30:00

 

? StringDateTime('15/3/2021', 'en-US')

 

returns a datetime value: 03/15/2021 00:00:00

 

? StringDateTime('15. März 2021 19:30', 'de-DE')

 

returns a datetime value: 03/15/2021 19:30:00

 

StringDecrypt(<encrypted>, <key>) : <string>

Given an encrypted string produced by StringEncrypt and the key used for encryption,  returns the decrypted original string.  

StringEmailValid(<email>) : <valid>

Given a string, returns a boolean true if the string is a valid email address.   The validation is permissive and should only be used to filter out strings that are certainly not email addresses.

 

? StringEmailValid('sales@manifold.net')

 

Returns boolean true.

 

? StringEmailValid('manifold.net')

 

Returns false.

 

? StringEmailValid('mailto:sales@manifold.net')

 

Returns true, an example of the permissive filtering, since mailto: followed by a valid email address is a valid URL used for emails.

 

? StringEmailValid('mailto:sales_manifold.net')

 

Returns false, since the mailto: is not followed by a valid email address.

 

? StringEmailValid('email: sales@manifold.net')

 

Returns false, as the string is not an email address or a URL to an email address.

 

StringEncrypt(<string>, <key>) : <encrypted>

Given a string and a second string to use as an encryption key, utilizes an AES256 encryption mechanism to return an encrypted string.    The encrypted string may later be decrypted using StringEncrypt.

StringEndsWith(<string>, <substring>) : <boolean>

Given a string and a substring returns True if the string ends with the substring using case-sensitive comparison. See the StringContains function for similar examples.

StringEndsWithCollate(<string>, <substring>, <collate-code>) : <boolean>

Given a string, a substring and a collation returns True if the string ends with the substring using the rules of the specified collation.    See the StringContainsCollate function for similar examples.

 

Collations are rules that specify how to compare text values.  The CollateCode function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.

StringEscape(<string>, <escape>) : <string>

Used to create strings that require character escaping to get around syntax rules for how some characters have special meaning in the syntax of functions or queries.   Given a first string and a second, escape string that specifies how a designated escape character is to be used to escape other characters, returns a string where the characters in the first string have been escaped as specified by the escape string.

 

? StringEscape('abc \'def\' ghi', '\\\'')

 

Returns:

 

abc \'def\' ghi

 

StringEscapeEncode(<string>, <escape>, <escapeUnprintable>) : <string>

From a string that may contain unprintable characters, such as line feeds and tabs, create strings that escape each unprintable character by prefixing the generally designated escape character plus a special escape character to the Unicode hexadecimal value for the unprintable characters.   Space characters are not escaped but passed as space characters.     The first argument is the string that may contain unprintable characters.  The second argument is the generally designated escape character.   The third argument is an additional escape character used only in front of unprintable character codes.

 

Consider the string created by the expression:

 

'abc ' + crlf + 'def'

 

Trying that in the Command Window,

 

? 'abc ' + crlf + 'def'

 

We get the result:

 

> ? 'abc' + crlf + 'def'

nvarchar: abc

def

 

The result is on two lines, since the crlf is a carriage return character followed by a line feed character.   The presence of such characters may cause problems when passing such a string in the syntax of functions or queries, so we can escape those characters into codes as follows:

 

? StringEscapeEncode('abc ' + crlf + 'def', '\\', 'x')

 

The result of that is:

 

> ? StringEscapeEncode('abc ' + crlf + 'def', '\\', 'x')

nvarchar: abc \x000D\x000Adef

 

Unpacking the above: The first argument in the function is the string containing the unprintable carriage return and line feed characters to be escaped.   The second argument specifies the general escape character, a \.   Since that character has meaning as an escape character in SQL syntax, it must itself be escaped and hence is specified with \\.   The third argument specifies the character, in this case x, that is to be prefixed before any Unicode for an unprintable character.   Printable characters and space characters are passed unmodified.

 

The result is that the crlf sequence of a carriage return character followed by a line feed character is escaped as:

 

\x000D\x000A

 

First comes the general escape character, \, followed by the special escape character for unprintable characters, x, followed by the Unicode hexadecimal value for a carriage return, 000D.   Following that comes an analogous sequence for the line feed character.

StringEscapeJson(<string>) : <string>

Takes a JSON string and automatically adds character escaping so it can safely be used in functions or queries.

StringFind(<string>, <substring>) : <position>

Given a string and a substring returns the position, beginning with 0,  in the string of the first character of the substring if the string contains the substring using case-sensitive comparison.   If the string does not contain the substring return -1.

StringFindCollate(<string>, <substring>, <collate-code>) : <position>

Given a string, a substring and a collation returns the position, beginning with 0,  in the string of the first character of the substring if the string contains the substring using the rules of the specified collation.   If the string does not contain the substring return -1.

 

In the examples that follow 928 is the collation code for en-US (US English) that is case sensitive.   930 is the collation code for en-US where case does not matter.

 

? StringFindCollate('Long John Silver','john', 928)

 

returns -1 because if case matters john does not occur in the first string.

 

? StringFindCollate('Long John Silver','john', 930)

 

returns 5 because if case does not matter the substring john begins at the sixth character in the first string.

 

Collations are rules that specify how to compare text values.  The CollateCode function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.  

StringFindNth(<string>, <substring>, <occurrence>) : <position>

Given a string, a substring and an <occurrence> number, n, (zero based counting, with the first <occurrence> being 0, the second being 1 and so on), returns the number of the character position in the text, also zero based, where the substring matches the nth time using case-sensitive comparison.   If not found at the specified nth position return -1.

StringFindNthCollate(<string>, <substring>, <occurrence>, <collate-code>) : <position>

Given a string, a substring and an <occurrence> number, n, (zero based counting, with the first <occurrence> being 0, the second being 1 and so on) and a collation, returns the number of the character position in the text, also zero based, where the substring matches the nth time using the rules of the specified collation.   If not found at the specified nth position return -1.

 

In the examples that follow 930 is the collation code for en-US (US English) that is not case sensitive.

 

? StringFindNthCollate('Long John Silver','L', 1, 930)

 

returns 12 because if case does not matter the second instance (indicated by 1 in zero based counting) of the substring 'L' occurs at the thirteenth character (indicated by 12 in zero based counting).

 

? StringFindNthCollate('Long John Silver','L', 2, 930)

 

returns -1 because there is no third instance of the substring 'L'.   

 

Collations are rules that specify how to compare text values.  The CollateCode function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.

StringFormat(<value>, <format>, <lang>): <string>

An "all in one" formatting function that takes an arbitrary data type value to format, a format pattern, a language, and returns a formatted string.    

 

The <value> can be any Manifold data type and will be formatted according to its type, using format options that would apply for a type-specific StringFormat<type> function of that type.  For example, if the <value> argument is for numeric data, then the format options that can be specified in <format> are the same as can be used for the StringFormatNumber function.

 

GEOMWKB values are currently reported as binary to avoid conversion, as done in previous builds.  Future builds will change that to report GEOMWKB values as geometry.

 

The number of bytes reported using B format by StringFormat and StringFormatBinary may differ from the number of bytes reported using B format by StringFormatGeom and StringFormatTile. The former two functions report the number of bytes in the value as it is stored in a .map file, with some compression already applied. The latter two functions report the number of bytes in the same value expanded to RAM, with no compression.

 

Choice Styles

 

Choice styles are available within StringFormat for numeric, numeric vector, and text values.  Choice styles are specified by a # format character followed by list of <value>=<show as> pairs, with each pair separated by a semicolon ; character.  The <value> in <value>=<show as> pairs can be either a number or a text value.

 

Using numeric values in <value>=<show as> pairs:

 

? StringFormat(128, '# 0=Black;128=Medium Gray;255=White', '')

 

Medium Gray

 

In the example above, the # character is followed by a list of three <value>=<show as> pairs, with semicolon characters separating the pairs in the list.  The first such pair is 0=Black, which means that a value of 0 will be shown as the string Black.   The second pair is 128=Medium Gray, which means that a value of 128 will be shown as the string Medium Gray.  The third pair is 255=White, which means that a value of 255 will be shown as the string White.

 

Using text values in <value>=<show as> pairs:

 

? StringFormat('LA', '# IA=Iowa;KS=Kansas;KY=Kentucky;LA=Louisiana', '')

 

Louisiana

 

or

 

? StringFormat('KS', '# IA=Iowa;KS=Kansas;KY=Kentucky;LA=Louisiana', '')

 

Kansas

 

Note the use of single quote ' characters to quote the string literal that is the first argument when using StringFormat with text values.   

 

When matching text values to a <value> in a <value>=<show as> pair, the text value is first trimmed, with leading and trailing spaces removed:

 

? StringFormat('KY', '# IA=Iowa;KS=Kansas;KY=Kentucky;LA=Louisiana', '')

 

and

 

? StringFormat('  KY', '# IA=Iowa;KS=Kansas;KY=Kentucky;LA=Louisiana', '')

 

and

 

? StringFormat('KY  ', '# IA=Iowa;KS=Kansas;KY=Kentucky;LA=Louisiana', '')

 

All result in

 

Kentucky

 

Since leading and trailing spaces in strings such as '  KY' or  'KY  ' are trimmed to form 'KY' before processing the value.

 

Values provided as the first argument which are not found in the list are returned as a string representation of that same value:

 

? StringFormat(77, '# 0=Black;128=Medium Gray;255=White', '')

 

77

 

or

 

? StringFormat('ID', '# IA=Iowa;KS=Kansas;KY=Kentucky;LA=Louisiana', '')

 

ID

 

Within the string defining the format, spaces are not significant except within the string that is the <show as> string in a <value>=<show as> pair:

 

? StringFormat(255, '#0=Black;128=Medium Gray;255=White', '')

 

White

 

Has the same effect as:

 

? StringFormat(255, '# 0 = Black ; 128 = Medium Gray; 255 = White', '')

 

White

 

When using the # Choice list format, it makes sense to compartmentalize long lists of <value>=<show as> pairs within global values, using the VALUE statement.

 

StringFormatBinary(<value>, <format>, <lang>): <string>

The function takes an arbitrary data type value to format, a format pattern, a language, and returns a formatted string.  While primarily intended to report on binary data, the function formats an arbitrary value for any data type (as if it were binary data) using one of the following formats:

 

  • b = type name: <varbinary>
  • B = number of bytes (default): <varbinary, XXX b>

 

The number of bytes reported using B format by StringFormat and StringFormatBinary may differ from the number of bytes reported using B format by StringFormatGeom and StringFormatTile. The former two functions report the number of bytes in the value as it is stored in a .map file, with some compression already applied. The latter two functions report the number of bytes in the same value expanded to RAM, with no compression.

StringFormatBoolean(<value>, <format>) : <string>

The function takes a boolean value to format, a format pattern, and returns a formatted string. The format can be either b or B to output TRUE as 1 and FALSE as 0, or t or T for output as true or false, with the case of the result controlled by the case of the pattern letter.  The default is t.

StringFormatDateTime(<date>, <format>, <lang>) : <string>

The function takes a datetime value to format, a format pattern, a language, and returns a formatted string. The language can be an empty string (neutral) or a language code like en-US or de-DE. The format pattern can be either a single letter that defines the whole format, or a combination of format parts.  An example:

 

StringFormatDateTime([Date], 'D', '')

 

Single-letter format patterns - Using an example value of 25 March 2020 13:45:00 and an example language of en-US, single-letter format patterns and their result are:

 

  • d = short date: 3/25/2020

  • D = long date: Wednesday, March 25, 2020

  • f = long date + short time:  Wednesday, March 25, 2020 1:45 PM

  • F = long date + long time:   Wednesday, March 25, 2020 1:45:00 PM

  • g = short date + short time:   3/25/2020 1:45 PM

  • G = short date + long time:   3/25/2020 1:45:00 PM

  • m = short month and day:   25 Mar

  • M = long month and day:   25 March

  • r or R = RFC1123.  The language does not matter and is always neutral: Wed, 25 Mar 2020 13:45:00 GMT

  • s = sortable.  The language does not matter:   2020-03-25T13:45:00

  • t = short time:   1:45 PM

  • T = long time:   1:45:00 PM

  • u = universal sortable, the language does not matter:   2020-03-25 13:45:00Z

  • y or Y = year and month:   March 2020

 

  • An empty format string is interpreted as G = short date + long time.

 

Specifying a language automatically uses that language's words for the names of days and months:

 

? StringFormatDateTime(#03/15/2021#, 'D', 'de-DE')

 

returns the nvarchar string: Montag, 15. März 2021

 

? StringFormatDateTime(#03/15/2021#, 'D', '')

 

returns the nvarchar string: Monday, 15 March 2021

 

? StringFormatDateTime(#03/15/2021#, 'D', 'haw-US')

 

(Hawaiian) returns the nvarchar string: Poʻakahi, 15 Malaki 2021

 

? StringFormatDateTime(#03/15/2021#, 'D', 'chr-US')

 

(Cherokee) returns the nvarchar string: ᎤᎾᏙᏓᏉᏅᎯ, ᎠᏅᏱ 15,2021

 

? StringFormatDateTime(#03/15/2021#, 'D', 'lkt-US')

 

(Lakota) returns the nvarchar string: Aŋpétuwaŋži, Ištáwičhayazaŋ Wí 15, 2021

 

Some languages might not have days of the week, such as Arabic in Saudi Arabia:

 

? StringFormatDateTime(#03/15/2021#, 'D', 'ar-SA')

 

returns the nvarchar string: 02/1442/شعبان

 

While Arabic in Qatar uses a different format:

 

? StringFormatDateTime(#03/15/2021#, 'D', 'ar-QA')

 

returns the nvarchar string: 15 2021 مارس

 

If the format string differs from any of the single-letter formats and is not empty, it is interpreted as a combination of the following format parts.  Two letter variations add a leading zero for one-digit numeric values and use two characters for text values, for example, 01 instead of 1, and PM instead of P.  

 

  • d or dd = day: 25

  • ddd = short week day:   Wed

  • dddd = long week day:   Wednesday

  • g or gg = era:   A.D.

  • h or hh = hour on a 12-hour scale:   1

  • H or HH = hour on a 24-hour scale:   13

  • m or mm = minute:   45

  • M or MM = month as a number:   3

  • MMM = short month:   Mar

  • MMMM = long month:   March

  • s or ss = second:   0

  • t or tt = time marker:   PM

  • y or yy = short year:   20

  • yyy or yyyy or yyyyy = long year, accepts up to five letters because some languages allow up to five:   2020

 

Any character or any character sequence different from the above patterns, for example, x or hhh, is copied without any changes. For example,

 

StringFormatDateTime([Date], 'd-MMM-yy h:mm', '')

 

results in 25-Mar-20 1:45

 

The default conversion of a datetime value to a string uses leading zeros for month, day and hour to be the same as the output of an empty format for neutral language.

 

Using an empty format string and an empty language string produces the same string as the default conversion to a string.   For example, StringFormatDateTime(..., '', '') produces the same result as CAST (... AS NVARCHAR).

StringFormatDurationDays(<days>, <format>, <lang>) : <string>

The function takes a number of days to format, a format pattern, a language, and returns a formatted string. The format pattern is a combination of the following format parts. Two letter variations add a leading zero for one-digit numeric values.

 

  • d = days

  • f or ff or fff or ffff or ... (up to nine f characters) = fractions of a second

  • h or hh or H or HH = hours

  • m or mm = minutes

  • s or ss = seconds

 

Format parts must be ordered from bigger to smaller, that is, days before hours, hours before minutes, and so on.

 

An empty format string is interpreted as s = seconds.

 

Examples:

 

? StringFormatDurationDays(1.133, '', '') -- 97891 (seconds)

? StringFormatDurationDays(1.133, 'd:hh', '') -- 1:03

? StringFormatDurationDays(1.133, 'h:mm:ss', '') -- 27:11:31

 

Using an empty format string and an empty language string produces the same string as the default conversion to a string.   For example, StringFormatDurationDays(..., '', '') produces the same result as CAST (... AS NVARCHAR).

StringFormatDurationSeconds(<seconds>, <format>, <lang>) : <string>

Same as StringFormatDurationDays, but the value to format is in seconds.  The function takes a number of seconds to format, a format pattern, a language, and returns a formatted string. The format pattern is a combination of the following format parts. Two letter variations add a leading zero for one-digit numeric values.

 

  • d = days

  • f or ff or fff or ffff or ... (up to nine f characters) = fractions of a second

  • h or hh or H or HH = hours

  • m or mm = minutes

  • s or ss = seconds

 

Format parts must be ordered from bigger to smaller, that is, days before hours, hours before minutes, and so on.

An empty format string is interpreted as s = seconds.

 

Examples:

 

? StringFormatDurationSeconds(97891, '', '') -- 97891 (seconds)

? StringFormatDurationSeconds(97891, 'd:hh', '') -- 1:03

? StringFormatDurationSeconds(97891, 'h:mm:ss', '') -- 27:11:31

 

Using an empty format string and an empty language string produces the same string as the default conversion to a string.   For example, StringFormatDurationSeconds(..., '', '') produces the same result as CAST (... AS NVARCHAR).

StringFormatGeom(<geom>, <format>, <lang>): <string>

The function takes a geom, geommfd, or geomWKB value to format, a format pattern, a language, and returns a formatted string.  The function formats a geometry value using one of the following formats:

 

  • b = identified as a geom:  <geom>

  • B = number of bytes in the geom: <geom, XXX b>

  • t  = type of geometry (default):  <geom, line>

  • T = the number of coordinates and for more branches than 1, the number of branches after a slash: <geom, line, 200 c / 3>

 

The StringFormatGeom query function detects geoms with Z values and reports them as areazlinez, or pointz.

 

The number of bytes reported using B format by StringFormat and StringFormatBinary may differ from the number of bytes reported using B format by StringFormatGeom and StringFormatTile. The former two functions report the number of bytes in the value as it is stored in a .map file, with some compression already applied. The latter two functions report the number of bytes in the same value expanded to RAM, with no compression.

StringFormatNumber(<value>, <format>, <lang>) : <string>

The function takes a numeric value to format, a format pattern, a language, and returns a formatted string. The format pattern consists of a single-letter format and an optional integer number specifying the desired number of digits or decimal digits, depending on the format.  The function uses a language-specific decimal separator for the default format, for example, using a decimal point for en-US as in 123456.789 and a comma for de-DE as in 123456,789.

 

Using an example value of 123456.7890123 and an example language of en-US, single-letter format patterns and their result are:

 

    • c or C = currency, with an optional number of decimals, with currency taken from the language group:   $123,456.79

    • d or D = decimal integer, with an optional total number of digits and optional leading zero. The language does not matter:   123457, or, for 'd08': 00123457

    • e or E = exponential, with an optional number of decimals. The default number of decimals is 6 regardless of the language:   1.234568e+05

    • f or F = fractional, with an optional number of decimals:   123456.79

    • n or N = fractional + digit groups, with an optional number of decimals:   123,456.79

    • x or X = hexadecimal integer, with an optional total number of digits and optional leading zero. The language does not matter:   1e241

    • # = choice style, using a list of <value>=<show as> pairs that follows the # character.

 

    • If the format string is empty or invalid, the number is formatted as the shortest form that preserves all significant digits. The language does not matter:   123456.7890123

    • Using upper or lower case e or E or x or X specifies the case of the resulting exponent character or hexadecimal letters.

 

Using an empty format string and an empty language string produces the same string as the default conversion to a string.   For example, StringFormatNumber(..., '', '') produces the same result as CAST (... AS NVARCHAR).

 

Currency formatting takes the digit group separator, decimal point character, and monetary symbol from the language argument:

 

? StringFormatNumber(123456.7890123, 'c', 'en-US')

 

$123,456.79

 

but for German,

 

? StringFormatNumber(123456.7890123, 'c', 'de-DE')

 

123.456,79 €

 

and for Russian,

 

? StringFormatNumber(123456.7890123, 'c', 'ru-RU')

 

123 456,79 ₽

 

The StringFormatNumber function is a handy way to convert to exponential or hexadecimal notation.   If we want to know what the number 148649389 is in hexadecimal notation, in the Command Window we can execute:

 

? StringFormatNumber(148649389, 'x', '')

 

to get a result of 8dc35ad.

 

If we prefer our hexadecimal numbers using upper case, we can use an upper case X character as the format option:

 

? StringFormatNumber(148649389, 'X', '')

 

to get a result of 8DC35AD.

 

Angle Styles

 

Numbers can also be formatted into strings using angle styles.  Angle styles allow display of numbers that represent angular values such as bearings, azimuth, or longitudes and latitudes in a variety of formats.  Angle styles can use multiple letters, beginning with a or A and concatenating additional style letter codes for <subformat>, <positive>, and <decimals>.    Angle styles can use lower case or upper case letters.  

 

The <subformat> code letter can be d, m, or s to specify formats using decimal degrees, degrees and decimal minutes, or degrees, minutes, and decimal seconds.  If the <subformat> letter code is empty or unrecognized, then s subformat is used, for degrees, minutes and decimal seconds.

 

The <positive> code letter can be N, E, S,or W to specify which hemisphere has positive angular values, with the opposite hemisphere using negative numbers.  When styling latitude numbers, use N to apply the convention that positive numbers are North latitudes and negative numbers are South latitudes.  When styling longitude numbers, use E to apply the convention that positive numbers are East longitudes and negative numbers are West longitudes.   The case of the code letter specifies whether the hemisphere letter displayed should be lower case or upper case.

 

The <decimal> code letter is a number that specifies how many decimal digits appear after the decimal point for the smallest unit in the format used.

 

Using an example numeric value of 10.687 and an example language of en-US, angle format patterns and their result are:

 

    • a = angle.  With no subformat specified, s subformat is used: 10º41'13.20"

 

    • d = appended to a, a subformat specification for decimal degrees: 10.69º

    • m = appended to a, a subformat specification for degrees and decimal minutes:  10º41.22'

    • s = appended to a, a subformat specification for degrees, minutes, and decimal seconds: 10º41'13.20"

 

    • N, E, S, or W = appended to a followed by a subformat letter, specifies which hemisphere has positive angular values, with negative values for the opposite hemisphere.  Using adN:  10.69º N

 

 

Examples using angle styles:

 

? StringFormatNumber(10.687, 'a', '')

 

10º41'13.20"

 

Specifying the d subformat:

 

? StringFormatNumber(10.687, 'ad', '')

 

10.69º

 

Specifying the m subformat:

 

? StringFormatNumber(10.687, 'am', '')

 

10º41.22'

 

Specifying the s subformat (default if no subformat is specified):

 

? StringFormatNumber(10.687, 'as', '')

 

10º41'13.20"

 

Specifying the s subformat with German language as used in Germany:

 

? StringFormatNumber(10.687, 'as', 'de-DE')

 

10º41'13,20"

 

Using d subformat, assuming the number is a latitude, specifying the N hemisphere as positive:

 

? StringFormatNumber(10.687, 'adN', '')

 

10.69º N

 

Using the same settings, how a negative number is formatted into a string:

 

? StringFormatNumber(-10.687, 'adN', ''

 

10.69º S

 

Using the same settings, a positive number with 1 digit after the decimal point (note the automatic rounding):

 

? StringFormatNumber(10.687, 'adN1', '')

 

10.7º N

 

Using the same settings, with 4 digits after the decimal point:

 

? StringFormatNumber(10.687521794, 'adN4', '')

 

10.6875º N

 

It is possible to format any number using angle styles, but using angle styles generally makes sense only for numbers that represent angular values, such as degrees, used in longitudes and latitudes.

 

Choice Styles

 

Choice styles are specified by a # format character followed by list of <value>=<show as> pairs, with each pair separated by a semicolon ; character:

 

? StringFormatNumber(128, '# 0=Black;128=Medium Gray;255=White', '')

 

Medium Gray

 

In the example above, the # character is followed by a list of three <value>=<show as> pairs, with semicolon characters separating the pairs in the list.  The first such pair is 0=Black, which means that a value of 0 will be shown as the string Black.   The second pair is 128=Medium Gray, which means that a value of 128 will be shown as the string Medium Gray.  The third pair is 255=White, which means that a value of 255 will be shown as the string White.

 

Values provided as the first argument which are not found in the list are returned as a string representation of that same value:

 

? StringFormatNumber(77, '# 0=Black;128=Medium Gray;255=White', '')

 

77

 

Within the string defining the format, spaces are not significant except within the string that is the <show as> string in a <value>=<show as> pair:

 

? StringFormatNumber(255, '#0=Black;128=Medium Gray;255=White', '')

 

White

 

Has the same effect as:

 

? StringFormatNumber(255, '# 0 = Black ; 128 = Medium Gray; 255 = White', '')

 

White

 

When using the # Choice list format, it makes sense to compartmentalize long lists of <value>=<show as> pairs within global values, using the VALUE statement.

 

 

StringFormatTile(<tile>, <format>, <lang>): <string>

The function takes a tile value to format, a format pattern, a language, and returns a formatted string.  The function formats a geometry value using one of the following formats:

 

  • b = identified as a tile:  <tile>

  • B = number of bytes in the tile: <tile, XXX b>

  • t  = width x height x channels, when there is more than one channel (default):  <tile, 128 x 128 x 3>

  • T = width x height, pixel type with number of channels: <tile, 128 x 128, float64x3>

 

The number of bytes reported using B format by StringFormat and StringFormatBinary may differ from the number of bytes reported using B format by StringFormatGeom and StringFormatTile. The former two functions report the number of bytes in the value as it is stored in a .map file, with some compression already applied. The latter two functions report the number of bytes in the same value expanded to RAM, with no compression.

StringFormatUuid(<uuid>, <format>) : <string>

The function takes an UUID value to format, a format pattern, and returns a formatted string. The format can be either x or X.  The use of upper or lower case controls the case controls the case of hexadecimal letters in the result. The default is x.

StringFormatVector(<valuexN>, <format>, <lang>) : <string>

The function takes a vector value to format, a format pattern, a language, and returns a formatted string. The format pattern is the same as for StringFormatNumber, including availability of angle styles and choice styles.  The formatted string consists of strings for the individual vector values separated using a list separator for the specified language, and enclosed in square brackets, for example, using comma , characters for list separators [ 138, 194, 159 ] for en-US and using semicolon ; characters for list separators [ 138; 194; 159 ]  for de-DE.   The function also uses a language-specific decimal separator for the default format, for example, using a decimal point for en-US as in [ 55.02, -47.46, -29.16 ] and a comma for de-DE as in [ 55,02; -47,46; -29,16 ].

 

Using an example value of 123456.7890123 and an example language of en-US, single-letter format patterns and their result are:

 

    • c or C = currency, with an optional number of decimals, with currency taken from the language group:   $123,456.79

    • d or D = decimal integer, with an optional total number of digits and optional leading zero. The language does not matter:   123457, or, for 'd08': 00123457

    • e or E = exponential, with an optional number of decimals. The default number of decimals is 6 regardless of the language:   1.234568e+05

    • f or F = fractional, with an optional number of decimals:   123456.79

    • n or N = fractional + digit groups, with an optional number of decimals:   123,456.79

    • x or X = hexadecimal integer, with an optional total number of digits and optional leading zero. The language does not matter:   1e241

    • # = choice style, using a list of <value>=<show as> pairs that follows the # character.

 

    • If the format string is empty or invalid, the number is formatted as the shortest form that preserves all significant digits. The language does not matter:   123456.7890123

    • Using upper or lower case e or E or x or X specifies the case of the resulting exponent character or hexadecimal letters.

 

Using an empty format string and an empty language string produces the same string as the default conversion to a string.   For example, StringFormatVector(..., '', '') produces the same result as CAST (... AS NVARCHAR).

 

Currency formatting takes the digit group separator, decimal point character, and monetary symbol from the language argument.  See examples for StringFormatNumber.

 

Angle Styles

 

Numbers in numeric vectors can also be formatted into strings using angle styles.  Angle styles allow display of numbers that represent angular values such as bearings, azimuth, or longitudes and latitudes in a variety of formats.  Angle styles can use multiple letters, beginning with a or A and concatenating additional style letter codes for <subformat>, <positive>, and <decimals>.    Angle styles can use lower case or upper case letters.  

 

The <subformat> code letter can be d, m, or s to specify formats using decimal degrees, degrees and decimal minutes, or degrees, minutes, and decimal seconds.  If the <subformat> letter code is empty or unrecognized, then s subformat is used, for degrees, minutes and decimal seconds.

 

The <positive> code letter can be N, E, S,or W to specify which hemisphere has positive angular values, with the opposite hemisphere using negative numbers.

 

The <decimal> code letter is a number that specifies how many decimal digits appear after the decimal point for the smallest unit in the format used.

 

Using an example numeric value of 10.687 and an example language of en-US, angle format patterns and their result are:

 

    • a = angle.  With no subformat specified, s subformat is used: 10º41'13.20"

 

    • d = appended to a, a subformat specification for decimal degrees: 10.69º

    • m = appended to a, a subformat specification for degrees and decimal minutes:  10º41.22'

    • s = appended to a, a subformat specification for degrees, minutes, and decimal seconds: 10º41'13.20"

 

    • N, E, S, or W = appended to a followed by a subformat letter, specifies which hemisphere has positive angular values, with negative values for the opposite hemisphere.  Using adN:  10.69º N

 

An important difference between StringFormatVector and StringFormatNumber is that when a <positive> code letter is used to designate a positive hemisphere, the hemisphere letter will alternate between the two vector components, between N and E or between S and W as follows:

 

  • If a <positive> code letter is specified and it is specified as E,  the first vector component is formatted as E and the second component as N.  This corresponds to typical X,Y vector longitude, latitude ordering and the convention that eastern hemisphere longitudes and northern hemisphere latitudes are positive.   
  • When N is specified in a positive option, the first vector component is formatted as N and the second component as E. That corresponds to "backwards" Y,X vector latitude, longitude ordering while retaining the convention that eastern hemisphere longitudes and northern hemisphere latitudes are positive.   This is a handy option for dealing with numeric vectors created by people who have not read the That YX Thing essay.
  • When W is specified in a positive option, the first vector component is formatted as W and the second component as S.  This corresponds to typical X,Y vector longitude, latitude ordering but using a backwards arrangement from the usual convention by representing eastern hemisphere longitudes and northern hemisphere latitudes as negative, with western hemisphere longitudes and southern hemisphere latitudes as positive.    
  • When S is specified in a positive option, the first vector component is formatted as S and the second component as W.  This corresponds to a double backwards arrangement of Y,X vector latitude, longitude ordering while using positive numbers for western hemisphere longitudes and southern hemisphere latitudes.   
  • When lower case positive options are specified, then lower case n, e, s, and w letters are used in the generated string.
  • If there are three components in the vector, the third component is formatted as a plain floating-point number, not using angle style.  That is a typical arrangement for a three component vector showing longitude, latitude, and a Z (height) or other numeric value.
  • If there are four components in the vector, the third value is formatted the same as the first value,  and the fourth value is formatted the same as the second value.  That is a typical arrangement for a vector of four values that represent two point locations, such as the beginning and end of a straight line segment.

 

Examples of numeric vectors formatted using the <positive> option, with alternation of hemisphere letter between the vector components, are shown in the Styling Table Fields topic.

 

Choice Styles

 

Choice styles are specified by a # format character followed by list of <value>=<show as> pairs, with each pair separated by a semicolon ; character:

 

? StringFormatVector(VectorMakeX3(255,0,128), '#0=Black;128=Medium Gray;255=White', '')

 

[ White, Black, Medium Gray ]

 

The same <value>=<show as> pair list is used for all of the components of the vector.  For additional examples that are similar, see the discussion for the StringFormatNumber function.   See also the Numeric Vector Choices section of the Styling Choices in Tables topic.

 

When using the # Choice list format, it makes sense to compartmentalize long lists of <value>=<show as> pairs within global values, using the VALUE statement.

 

StringGmlCoordSystem(<gml>) : <system>

 

Given a GML string return a string reporting the coordinate system specified within that GML string.

StringGmlGeom(<gml>) : <geom>

Convert GML geometry into a Manifold geom:  Given a GML string return a geom that contains the geometry specified in the GML string.

StringJsonArrayValue(<json>, <index>): <value>

Parses a JSON array and returns the string value at the specified index.

StringJsonBoolean(<json>, <name>, <strict>) : <value>

Extract a boolean value from a named JSON boolean entity.  Given a JSON string find the specified named entity if it exists and if it has a boolean value return that value.  If the named entity does not exist or if it has not been assigned a boolean value, return NULL.   The <strict> option when True requires a boolean value and rejects string representations such as "true".   When False, any other representations for the boolean value, such as string representations, are accepted.  

 

In the following examples a JSON string has two named entities that are intended as booleans: roads and canals.  In the case of the roads entity the boolean value of true  is specified using a boolean, that is just the word true or false not enclosed in quotes.   In the case of the canals entity the boolean value of false is specified using a string, that is, text enclosed in double quote " characters.

 

? StringJsonBoolean('{ "roads": true, "canals": "false" }', 'roads', true)

 

When the <strict> option is true, the above expression returns:

 

boolean: 1

 

? StringJsonBoolean('{ "roads": true, "canals": "false" }', 'canals', true)

 

When the <strict> option is true, the above expression returns:

 

boolean: <NULL>

 

The NULL was returned because a strict requirement that rejects any non-boolean representations leaves the canals entity with no value.   The function only returns a boolean value if the named entity exists and also has a boolean value.

 

? StringJsonBoolean('{ "roads": true, "canals": "false" }', 'roads', true)

 

When the <strict> option is false, the above expression returns:

 

boolean: 1

 

? StringJsonBoolean('{ "roads": true, "canals": "false" }', 'canals', true)

 

When the <strict> option is false, the above expression returns:

 

boolean: 0

 

StringJsonGeoCoordSystem(<json>) : <system>

Extract the coordinate system from a GeoJSON string. Given a GeoJSON  string return a string reporting the coordinate system specified within that GeoJSON  string.  

 

StringJsonGeoGeom(<json>) : <geom>

Convert GeoJSON geometry into a Manifold geom:  Given a GeoJSON string return a geom that contains the geometry specified in the GeoJSON string.

StringJsonNumber(<json>, <name>, <strict>) : <value>

Extract a numeric value from a named JSON boolean entity.  Given a JSON string find the specified named entity if it exists and if it has a numeric value return that value.  If the named entity does not exist or if it has not been assigned a numeric value, return NULL.   The <strict> option when True requires a numeric value and rejects string representations such as "true".   When False, any other representations for the numeric value, such as string representations, are accepted.  

 

See the examples for the StringJsonBoolean function for use of <strict> to require a specific type.

StringJsonObject(<json>, <name>) : <value>

Extract an object as a string value from a named JSON boolean entity.  Given a JSON string find the specified named entity if it exists and if it has an object as a string value return that value.  If the named entity does not exist or if it has not been assigned a string value, return NULL.   

StringJsonTile(<json>, <cx>, <cy>, <channels>, <strict>): <tile>

Takes a JSON string, the number of pixels by X and Y, and the number of channels and parses the string into a tile. The format is the same as used by TileJson. The function also takes a boolean <strict> flag which defines what to do when the number of values in the string is more or less than stated by the  X * Y * channels arguments.   If <strict> is true, the function returns NULL, otherwise if there are more values than implied by the given arguments the function returns a completed tile ignoring the extra values in the string, or there are less values, an incomplete tile padded with invisible pixels.

StringJsonValue(<json>, <name>, <strict>) : <value>

Extract a string value from a named JSON boolean entity.  Given a JSON string find the specified named entity if it exists and if it has a string value return that value.  If the named entity does not exist or if it has not been assigned a string value, return NULL.   The <strict> option when True requires a string value and rejects any other representations.  When False, any other representations for the string value are accepted.  

 

See the examples for the StringJsonBoolean function for use of <strict> to require a specific type.

StringJsonArray(<json>, <name>) : <value>

Extract an array element as a string value from a named JSON boolean entity.  Given a JSON string find the specified named entity if it exists and if it has an array element return that array as a string value.  If the named entity does not exist or if it has not been assigned an array element, return NULL.  

 

The string returned could be parsed with the StringToJsonArrayValues function.

StringLength(<string>) : <length>

Given a string returns the number of characters in the string.

 

? StringLength('Manifold')

 

StringPadEnd(<string>, <length>, <pad>) : <string>

Given a string, a desired length in number of characters and a string to use as padding, return a string truncated or padded at the end to the desired length as follows:

 

  • If the desired length is less than the length of the original string, truncate the original string (cutting characters off the end) to the desired length and return.
  • If the desired length is greater than the length of the original string, add characters from the padding string to the end of the original string, repeating the padding string as necessary, to fill out the desired length.
  • Negative lengths result in no padding.

 

? StringPadEnd('Sonora', 4, 'Radian')

 

Returns:

 

Sono

 

Example:

 

? StringPadEnd('Sonora', 20, 'Radian')

 

Returns:

 

SonoraRadianRadianRa

 

Example:

 

? StringPadEnd('Sonora', 20, '.')

 

Returns:

 

Sonora..............

 

StringPadStart(<string>, <length>, <pad>) : <string>

Given a string, a desired length in number of characters and a string to use as padding, return a string truncated or padded at the beginning to the desired length as follows:

 

  • If the desired length is less than the length of the original string, truncate the original string (cutting characters off the end) to the desired length and return.
  • If the desired length is greater than the length of the original string, add characters from the padding string to the beginning of the original string, repeating the padding string as necessary, to fill out the desired length.
  • Negative lengths result in no padding.

 

? StringPadStart('Sonora', 4, 'Radian')

 

Returns:

 

Sono

 

Example:

 

? StringPadStart('Sonora', 20, 'Radian')

 

Returns:

 

RadianRadianRaSonora

 

Example:

 

? StringPadStart('Sonora', 20, '.')

 

Returns:

 

..............Sonora

 

StringRegexpCount(<string>, <regexp>, <flags>) : <matches>

Given a string, a  regular expression pattern and a flag to use case or to not use case, returns the number of matches to that regular expression found within the string.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpCount('Netherlands', 'n', 'c')

 

Returns 1 for the single lower case n character in the string.

 

? StringRegexpCount('Netherlands', 'n', 'i')

 

Returns 2 since if case is ignored both the upper case N and the lower case n match the regular expression 'n'.

StringRegexpFind(<string>, <regexp>, <flags>) : <position>

Given a string, a  regular expression pattern and a flag to use case or to not use case, returns the index (zero based counting) to the position of the first occurrence of that regular expression found within the string and returns -1 if no match is found.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpFind('Netherlands', 'n', 'c')

 

Returns 8 (meaning the 9th character) for the single lower case n character in the string which occurs as the ninth character.  

 

? StringRegexpFind('Netherlands', 'n', 'i')

 

Returns 0 (meaning the 1st character) since if case is ignored the upper case N at the first character position matches the regular expression 'n'.

StringRegexpFindNth(<string>, <regexp>, <occurrence>, <flags>) : <position>

Given a string, a  regular expression pattern, an occurrence number n (zero based counting) and a flag to use case or to not use case, returns the index (zero based counting) to the position of the nth occurrence of that regular expression found within the string and returns -1 if no match is found.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpFindNth('Netherlands', 'n', 1, 'i')

 

Returns 8 (meaning the 9th character) since if case is ignored the lower case n at the ninth character position is the second occurrence (in zero based counting 1 means the 2nd occurrence) of a match to the regular expression 'n'.

StringRegexpMatches(<string>, <regexp>, <flags>) : <boolean>

Given a string, a  regular expression pattern and a flag to use case or to not use case, returns True if the string exactly matches the regular expression and 0 for False otherwise.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.   This function is used instead of "LIKEX" query operators found in some query engines.

 

? StringRegexpMatches('Netherlands', 'n.*', 'c')

 

Returns 0 (meaning False) since the string does not begin with a lower case n character.

 

? StringRegexpMatches('Netherlands', 'n.*', 'i')

 

Returns 1 (meaning True) since if case is ignored the upper case N at the first character position matches the regular expression 'n*', that is, text which begins with the letter n or N and then has zero or more of any characters following.

StringRegexpReplace(<string>, <regexp>, <target>, <flags>) : <string>

Given a string, a  regular expression pattern, a target replacement string and a flag to use case or to not use case, returns a string where all substrings of characters that match the regular expression have been replaced by the target replacement string.  If no matches are found returns the original string.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpReplace('Netherlands', 'n', 'y', 'i')

 

Returns:

 

yetherlayds

 

Example:

 

? StringRegexpReplace('Netherlands', 'n', 'y', 'c')

 

Returns:

 

Netherlayds

 

Example:

 

? StringRegexpReplace('Netherlands', 'n.th.r', 'Upper', 'i')

 

Returns:

 

Upperlands

 

See the Example: Unique Names using Regular Expressions topic for a practical example using this function.

 

StringRegexpReplaceNth(<string>, <regexp>, <target>, <occurrence>, <flags>) : <string>

Given a string, a  regular expression pattern, an occurrence number n (zero based counting) and a flag to use case or to not use case,returns a string where the nth  substring of characters that match the regular expression has been replaced by the target replacement string.  If no matches are found returns the original string.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpReplaceNth('Mississippi', 's', 't', 0, 'c')

 

Returns:

 

Mitsissippi

 

Example:

 

? StringRegexpReplaceNth('Mississippi', 'S', 't', 3, 'i')

 

Returns:

 

Missistippi

 

Example:

 

? StringRegexpReplaceNth('Mississippi', 'ss', 'tt', 1, 'c')

 

Returns:

 

Missittippi

 

StringRegexpSubstring(<string>, <regexp>, <flags>) : <string>

Given a string, a  regular expression pattern and a flag  to use case or to not use case, returns the first substring found that matches the regular expression within the string and returns an empty string if no match is found.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpSubstring('Netherlands', 'n.*l', 'i')

 

Returns:

 

Netherl

 

 

StringRegexpSubstringNth(<string>, <regexp>, <occurrence>, <flags>) : <string>

Given a string, a  regular expression pattern, an occurrence number n (zero based counting) and a flag  to use case or to not use case, returns the nth substring found that matches the regular expression within the string and returns an empty string if no match is found.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpSubstringNth('Netherlands', 'n..', 1, 'i')

 

Returns:

 

nds

 

Since the <occurrence> argument of 1 means the second occurrence (zero based counting) of the character n followed by two other characters, case insensitive.

StringReplace(<string>, <source>, <target>) : <string>

Given a string, a source string and a target string, return a modified string with all occurrences of the source string, using case-sensitive comparison, replaced by the target string.   If no instances of the source string are found, returns the original string.   

 

? StringReplace('Long John Silver','Long', 'Short')

 

StringReplaceCollate(<string>, <source>, <target>, <collate-code>) : <string>

Given a string, a source string, a target string and a collation return a modified string with all occurrences of the source string, using the rules of the specified collation,  replaced by the target string.   If no instances of the source string are found, returns the original string.   

 

In the example that follows 930 is the collation code for en-US (US English) that is not case sensitive.

 

? StringReplaceCollate('Long John Silver','long', 'Short', 930)

 

The above expression returns:

 

nvarchar: Short John Silver

 

Collations are rules that specify how to compare text values.  The CollateCode  function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.

StringReplaceNth(<string>, <source>, <target>, <occurrence>) : <string>

Given a string, a source string, a target string, and an <occurrence> number n (zero based counting, with the first <occurrence> being 0, the second being 1 and so on), return a modified string with the nth occurrence of the source string replaced by the target string.  If the source string is not found at the specified nth position returns the original string.

StringReplaceNthCollate(<string>, <source>, <target>, <occurrence>, <collate-code>) : <string>

Given a string, a source string, a target string, an <occurrence> number, n, (zero based counting, with the first <occurrence> being 0, the second being 1 and so on) and a collation, return a modified string with the nth occurrence of the source string, using the rules of the specified collation,  replaced by the target string.  If the source string is not found at the specified nth position returns the original string.

 

In the examples that follow 930 is the collation code for en-US (US English) that is not case sensitive.

 

? StringReplaceNthCollate('Long John Silver','L', 'X', 1, 930)

 

The above expression returns:

 

nvarchar: Long John SiXver

 

? StringReplaceNthCollate('Long John Silver','L', 'X', 2, 930)

 

The above expression returns the original string since there is no third occurrence of the source string 'L'.

 

Collations are rules that specify how to compare text values.  The CollateCode  function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.

StringReverse(<string>) : <string>

Given a string returns a string with the characters in reverse order.

 

? StringReverse('Netherlands')

 

Returns:

 

sdnalrehteN

 

StringSoundex(<string>) : <string>

Given a string returns the Soundex code for that string.  The Soundex algorithm generates short strings of alphanumeric codes based on how an English word sounds.   English words that are pronounced with similar sounds have the same Soundex codes.   

 

? StringSoundex('Sonora')

 

Returns:

 

S560

 

Example:

 

? StringSoundex('Sanara')

 

Returns:

 

S560

 

Soundex codes may be used to find matches to words entered by users who use inexact spelling.   For example, if we have a table of provinces in Mexico and a user enters Sanara we could use a query to find possible matches:

 

SELECT [NAME] FROM [Mexico Table]

  WHERE StringSoundex('Sanara') = StringSoundex([NAME]);

 

Returns a results table with one record, Sonora, because both of the words Sanara and Sonora have the same Soundex code as far as the English-based Soundex algorithm is concerned.

StringEnd(<string>, <length>): <string>

Given a string and a numeric length N, returns the last N characters of the string.   If the specified number of characters is negative, the entire string is returned, consistent with the behavior of StringSubstringLen.

 

? StringEnd('Netherlands', 5)

 

Returns:

 

lands

 

StringStart(<string>, <length>): <string>

Given a string and a numeric length N, returns the first N characters of the string.   If the specified number of characters is negative, the entire string is returned, consistent with the behavior of StringSubstringLen.

 

? StringStart('Netherlands', 6)

 

Returns:

 

Nether

 

StringStartsWith(<string>, <substring>) : <boolean>

Given a string and a substring returns True if the string starts with the substring using case-sensitive comparison. See the StringContains function for similar examples.

StringStartsWithCollate(<string>, <substring>, <collate-code>) : <boolean>

Given a string, a substring and a collation returns True if the string starts with the substring using the rules of the specified collation.    See the StringContainsCollate function for similar examples.

 

Collations are rules that specify how to compare text values.  The CollateCode  function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.

StringSubstring(<string>, <start>) : <string>

Given a string and a start position (zero based counting) returns the substring from  the start position to the end of the original string.  Negative start values result in a start at the beginning of the string, equivalent to a start of 0.

 

? StringSubstring('Netherlands', 3)

 

Returns:

 

herlands

 

StringSubstringLen(<string>, <start>, <length>) : <string>

Given a string, start position (zero based counting), and a length returns the substring of the specified length beginning at the start position  of the original string.  Negative length values result in the full length of the string being used.

 

? StringSubstringLen('Netherlands', 3, 4)

 

Returns:

 

herl

 

To get a sequence of desired characters at the start of a string, we specify a start position of 0, the first character, as well as the length of the desired substring:

 

? StringSubstringLen('Netherlands', 0, 4)

 

Returns:

 

Neth

 

StringToCharacters(<string>) : <table>

Split a string into a table of characters.  Given a string returns a table where each row contains a character in the string.

 

? Call StringToCharacters('Netherlands')

 

Returns:

 

 

StringToJsonObjectValues(<json>) : <table>

Split a JSON string into objects.   Given a JSON string returns a table where each row contains an object string for an object contained in that JSON string.

StringToJsonArrayValues(<json>) : <table>

Split a JSON string into array values.   Given a JSON string returns a table where each row contains an array value for an array contained in that JSON string.

StringToLowerCase(<string>) : <string>

Given a string returns the same string all in lower case.

StringToLowerCaseCollate(<string>, <collate-code>) : <string>

Given a string and a collation returns the same string all in lower case using the rules of the specified collation.  Ignores <useCase> option in the encoded collation since the result is all lower case.

 

Collations are rules that specify how to compare text values.  The CollateCode function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.

StringToRegexpMatches(<string>, <regexp>, <flags>) : <table>

Given a string, a  regular expression pattern and a flag  to use case or to not use case, returns a table where each row contains a substring found that matches the regular expression within the string.

 

? CALL StringToRegexpMatches('Mississippi', 'i.', 'c')

 

Returns

 

 

StringToTitleCase(<string>) : <string>

Requires Windows 7 or more recent Windows edition.  Given a string returns the same string in title case, converting the initial letter of each word into upper case and converting other characters into lower case, except that words which are already all upper case are left all upper case.

 

? StringToTitleCase('a walk in the park')

 

Returns:  A Walk In The Park

 

? StringToTitleCase('a WALK in the park')

 

Returns:  A WALK In The Park

 

? StringToTitleCase('a waLK iN tHe pARK')

 

Returns:  A Walk In The Park

 

? StringToTitleCase('A WALK IN THE PARK')

 

Returns:  A WALK IN THE PARK

 

To apply title case Manifold uses a Windows system facility, which reckons that a word which is in all capital letters is already in title case.  Strings that consist of all upper case characters therefore will not be modified. To transform such strings into a form where each word is lower case except for an initial capital letter, first  use the StringToLowerCase  function to transform the string into all lower case and then use the StringToTitleCase function.  

 

Contrary to most literary styles used in English, Windows also will capitalize articles, conjunctions and prepositions such as "of" and "the," so this function is best understood as Windows-style title case and not title case as understood in literary circles.

StringToTitleCaseCollate(<string>, <collate-code>) : <string>

Requires Windows 7 or more recent Windows edition.  Given a string and a collation returns the same string in title case using the rules of the specified collation.  Ignores <useCase> option in the encoded collation since the result is title case.  See the StringToTitleCase function for what is reckoned to be Windows-style title case.

 

Collations are rules that specify how to compare text values.  The CollateCode function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.  

StringToTokens(<string>, <separator>) : <table>

Split a string into substrings bounded by a given separator character.   Given a string and a separator string containing one character (such as a space character), returns a table where each row contains the substrings of the original string that were bounded by the separator.  

 

? CALL StringToTokens('Long John Silver', ' ')

 

Returns:

 

 

StringToUpperCase(<string>) : <string>

Given a string returns the same string all in lower case.

StringToUpperCaseCollate(<string>, <collate-code>) : <string>

Given a string and a collation returns the same string all in upper case using the rules of the specified collation.  Ignores <useCase> option in the encoded collation since the result is all upper case.

 

Collations are rules that specify how to compare text values.  The CollateCode function generates a number for a specific rule that can be used as the <collate-code> argument in functions that use collations.  See the COLLATE topic.

StringTranslate(<string>, <source>, <target>) : <string>

Given a string, a source string and a target string, returns a string where characters in the original string that are found in the  source string have been replaced on a character-by-character basis by the corresponding character in the target string.

 

? StringTranslate('Netherlands', 'abcde', 'xyz12')

 

Returns:

 

N2th2rlxn1s

 

The e characters were replaced by 2, the a character was replaced by x and the d character was replaced by 1.

StringTrim(<string>, <trim>) : <string>

Given a string and a trim string, removes all occurrences, including repeated occurrences, of the trim string at the beginning or the end of the string and returns the trimmed string.  Often used with a trim string of just one character, a ' ' space character, to trim extra spaces from the beginning and end of a string.  Often used to trim other text as well.

 

? StringTrim('"/info/products.shtml"', '"')

 

Returns:

 

/info/products.shtml

 

StringTrimEnd(<string>, <trim>) : <string>

Given a string and a trim string, removes all occurrences, including repeated occurrences, of the trim string at the end of the string and returns the trimmed string.  Often used with a trim string of just one character, a ' ' space character, to trim extra spaces from the end of a string.  Often used to trim other text as well.

 

? StringTrimEnd('<a href="/info/products.shtml">', '">')

 

Returns:

 

<a href="/info/products.shtml

 

StringTrimStart(<string>, <trim>) : <string>

Given a string and a trim string, removes all occurrences, including repeated occurrences, of the trim string at the beginning of the string and returns the trimmed string.  Often used with a trim string of just one character, a ' ' space character, to trim extra spaces from the beginning of a string.  Often used to trim other text as well.

 

? StringTrimStart('<a href="/info/products.shtml', '<a href="')

 

Returns:

 

/info/products.shtml

 

Example, combining the StringTrimStart and StringTrimEnd functions (written as a single line in the Command Window):

 

? StringTrimStart(StringTrimEnd('<a href="/info/products.shtml">', '">'), '<a href="')

 

Returns:

 

/info/products.shtml

 

StringUnescape(<string>, <escape>) : <string>

Remove escaping sequence from strings that use character escaping.   Given a first string and a second, escape string that specifies how a designated escape character sequence is to be used to escape other characters, returns a string where the characters in the first string have had character escaping,  escaped as specified by the escape string, removed.

 

? StringUnescape('abc \'def\' ghi', '\\\'')

 

Returns:

 

abc 'def' ghi

 

StringUnescapeDecode(<string>, <escape>, <escapeUnprintable>) : <string>

 

StringUnescapeJson(<string>) : <string>

Takes a JSON string to which character escaping has been added (such as by the StringEscapeJson function) and removes the character escaping.

StringUrlAppend(<url>, <urlRelative>): <url>

Given string arguments for a base <url> and a relative path <urlRelative>, return a string that is a valid URL with the relative path URL appended to the base URL.

 

? StringUrlAppend('http://manifold.net', 'files/books.mxb')

 

Returns:

 

nvarchar: http://manifold.net/files/books.mxb

 

StringUrlExtra(<url>): <string>

Given a URL string return extra URL text extracted from that URL.

 

?StringUrlExtra('https://Alice:654321@ecdc.europa.eu:8443/maps/cases/csv?id=33')

 

Returns:

 

nvarchar: ?id=33

 

StringUrlHost(<url>): <string>

Given a URL string return the host string extracted from that URL.

 

? StringUrlHost('https://Alice:654321@ecdc.europa.eu:8443/maps/cases/csv')

 

Returns:

 

nvarchar: ecdc.europa.eu

 

StringUrlMake(<scheme>, <host>, <port>, <user>, <password>, <path>, <extra>): <url>

Given string arguments for various parts of a URL, and a numeric argument for the port, return a string that is a correctly formed URL.  Empty string argument, using two single quote '   characters with no space character in between, are ignored.

 

? StringUrlMake('http', 'manifold.net', 0, '', '', '/files/books.mxb', '')

 

Returns:

 

nvarchar: http://manifold.net/files/books.mxb

 

StringUrlPassword(<url>): <string>

Given a URL string return the password string extracted from that URL.

 

? StringUrlPassword('https://Alice:654321@ecdc.europa.eu:8443/maps/cases/csv')

 

Returns:

 

nvarchar: 654321

 

StringUrlPath(<url>): <string>

Given a URL string return the path string extracted from that URL.

 

? StringUrlPath('https://Alice:654321@ecdc.europa.eu:8443/maps/cases/csv')

 

Returns:

 

nvarchar: /maps/cases/csv

 

StringUrlPort(<url>): <value>

Given a URL string return the port number extracted from that URL.

 

? StringUrlPort('https://Alice:654321@ecdc.europa.eu:8443/maps/cases/csv'))

 

Returns:

 

float64: 8443

 

StringUrlScheme(<url>): <string>

Given a URL string return the scheme string extracted from that URL.

 

? StringUrlScheme('https://Alice:654321@ecdc.europa.eu:8443/maps/cases/csv')

 

Returns:

 

nvarchar: https

 

StringUrlUser(<url>): <string>

Given a URL string return the user string extracted from that URL.

 

? StringUrlUser('https://Alice:654321@ecdc.europa.eu:8443/maps/cases/csv')

 

Returns:

 

nvarchar: Alice

 

StringUrlValid(<url>) : <valid>

Given a string, returns a boolean true if the string is a valid URL.   URLs are remarkably broad in terms of what is allowed.

 

? StringUrlValid('https://manifold.net')

 

Returns boolean true.

 

? StringUrlValid('httpz://manifold.net')

 

Returns boolean true.

 

? StringUrlValid('manifold.net')

 

Returns false.  A domain name by itself is not a URL.

 

? StringUrlValid('mailto:sales@manifold.net')

 

Returns true.

 

? StringUrlValid('mailto:sales_manifold.net')

 

Returns true, since the mailto: makes it a URL and what follows could be custom processed.

 

? StringUrlValid('file:///c:/data/bronzes/49.jpg')

 

Returns true, being a URL that references a local file.

 

StringWktGeom(<wkt>) : <geom>

Convert WKT geometry into a Manifold geom:  Given a WKT string return a geom that contains the geometry specified in the WKT string.

 

? StringWktGeom('POINT(10.687 59.8876)')

 

The expression above returns a geom that contains a point.

 

 

Notes

New functions - The list in this topic is intended to be comprehensive but might be not up to date.  New items are often added with updates to Manifold and may appear in the product before they appear in this documentation.   See the list in the query builder tab of the Command Window for an authoritative list of operators, commands and functions.

 

String functions using regular expressions:   See the Regular Expressions topic for allowed syntax for the <regexp> argument.   The <flags> argument must be provided and is either 'i' to ignore case or is 'c' for case matters.

 

Division by zero - returns NULL.

 

All types of values - Operators and functions generally  support all types of values so that, for example, we can use comparison or boolean operators on tiles and not just on scalar values.  

 

See Also

Tables

 

Add an Index to a Table

 

Functions

 

Queries

 

Regular Expressions

 

How Matrix Filters Work

 

Command Window

 

Command Window - Query Builder

 

Identifiers, Constants and Literals

 

SQL Statements

 

SQL Operators

 

SQL Functions

 

Aggregate SQL Functions

 

Coord SQL Functions

 

Geom SQL Functions

 

Tile SQL Functions

 

Other SQL Functions

 

Temporary Databases

 

EXECUTE

 

Example: Create and Run a Query -  See how the different parts of a command window operate when creating and running SQL queries.   Includes use of the Log tab as well as the ?expression and !fullfetch commands.

 

Example: Transfer DEM Terrain Heights to Areas in a Drawing - Given a map with an image layer that shows terrain heights taken from a DEM, and a drawing layer that contains areas, using a small SQL query we transfer the average terrain height within each area to that area as a Height attribute for the area. Easy!

 

SQL Example: Process Images with 3x3 Filters -  Shows a step-by-step example of developing an SQL query that takes a query written by the Edit Query button and then modifies that query into a general purpose query that can apply any 3x3 filter.   This makes it easy to use matrix filters we find on the web for custom image processing.   We extend the query by using parameters and adding a function, and then show how it can be adapted to use a 5x5 filter.

 

SQL Example: Process Images using Dual 3x3 Filters  - A continuation of the above topic, extending the example query to utilize two filters for processing, as commonly done with Sobel and Prewitt two filter processing.

 

SQL Example: Process RGB Images using Matrix Filters - A continuation of the above two topics, extending the example query to process three channel, RGB images.

 

SQL Example: Miscellaneous SQL Functions - A variety of small examples using SQL functions.

 

SQL Example: GeomOverlayAdjacent Function - Using the GeomOverlayAdjacent function, an example that shows how this function and similar functions such as GeomOverlayContained, GeomOverlayContaining, GeomOverlayIntersecting and GeomOverlayTouching operate.

 

SQL Example: GeomOverlayTopologyUnion Function - A continuation of the SQL Example: GeomOverlayAdjacent Function example, using the GeomOverlayTopologyUnion function, an example that shows how this function and similar functions such as GeomOverlayTopologyIdentity, GeomOverlayTopologyIntersect and GeomOverlayTopologyUpdate operate.

 

Example: Merge : areas (dissolve) - In this example we combine multiple area objects into a single area object by using the Merge template in the Transform pane, using the areas (dissolve) option.  A drawing of French regions shows some regions as more than one area.  We would like each region to be one area so the table of regions has one record per region.

 

SQL Example: Learn SQL from Edit Query - Merging Areas - We learn how to write an SQL query that does a Merge : area (dissolve) operation by cutting and pasting from what the Edit Query button automatically generates.

 

Example: Use a Transform Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform pane to create three different sizes of buffers for different lines in a drawing and then automatically create a query which does the same thing.  Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Clip Areas with a Transform Expression - Use the Expression tab of the Transform pane to clip areas in a drawing to fit within horizontal bounds.   Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Transform Templates, Expressions and Queries - We learn to use a function by clicking on a template in the Transform pane, seeing what it does in a preview, looking at the query Manifold creates and then trying out the function in the Expression tab.

 

SQL Example: List Transformation Grids - In this example we use a few snippets of easy SQL to list NTv2 and NADCON transformation grids that are available within the grids.dat compressed collection of transformation grid files.   Grid transformation files can be used when creating custom base coordinate systems, for NADCON / HARN / HPGN and NTv2 high accuracy transformations that use grid files.