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, as used within tables and attributes for drawings.   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 SQL 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

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.

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

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

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>

 

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.

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.

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

 

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.

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 lengths (zero based counting) 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://www.manifoldgis.com', 'files/books.mxb')

 

Returns:

 

nvarchar: http://www.manifoldgis.com/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', 'www.manifoldgis.com', 0, '', '', '/files/books.mxb', '')

 

Returns:

 

nvarchar: http://www.manifoldgis.com/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

 

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

 

SQL Constants and Literals

 

SQL Statements

 

SQL Operators

 

SQL Functions

 

Aggregate SQL Functions

 

General SQL Functions

 

Coord SQL Functions

 

Geom SQL Functions

 

Tile 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: Union Areas - Combine multiple area objects into a single area.   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: Learning to Union Areas in SQL from Edit Query - We learn how to write an SQL query that does a custom Union Areas 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.