Teradata String Functions are primarily used for various string manipulation. It also supports most of the standard string function along with the Teradata extension to those functions.
Teradata String Functions
Below are the commonly used Teradata string functions:
Read:
| Function | Description |
| concat(string1, …, stringN) | Returns the concatenation of two or more string values. This function provides the same functionality as the SQL-standard concatenation operator (||). |
| length(string) | Returns number of characters in the string |
| lower(string) | Converts string to lower case |
| upper(string) | Converts string to upper case |
| lpad(string, size, padstring) | Pads the left side of string with characters to create a new string. |
| rpad(string, size, padstring) | Pads the right side of string with characters to create a new string. |
| ltrim(string) | Removes leading whitespaces from string |
| rtrim(string) | Removes training whitespaces from string |
| replace(string, search) | Remove search string from the given string |
| replace(string, search, replace) | Replace all instances of search with replace string |
| reverse(string) | Return string characters in reverse order |
| split(string, delimiter) | Split given string on delimiter. This function returns array of string |
| strpos(string, substring) | Return staring position first instance of substring in given string |
| position(substring IN string) | Return staring position first instance of substring in given string |
| substr(string, start, length) | Returns a substring of string that begins at positionstart and is length characters long |
| trim(string) | Removes leading and trailing whitespace from given string |
| chr(n) | Returns the character with the specified ASCII value. |
| to_utf8(string) | Encodes string into a UTF-8 varbinary representation. |
| from_utf8(binary) | Decodes a UTF-8 encoded string from binary |
| select otranslate(string, from, to); | Replaces any character in string that matches a character in the from set with the corresponding character in the to set |
Teradata String Functions Examples
Below are some of sample example on Teradata string functions.
Teradata pad zero using String functions:
select lpad('1234',8,'0'); lpad('1234',8,'0') 00001234 select rpad('1234',8,'0'); rpad('1234',8,'0') 12340000Teradata Sub-string function Examples:
select substr('1234789464361',1,5); Substr('1234789464361',1,5) 12347The Teradata Translate function Examples:
select otranslate('123CDR123','CDR','123'); otranslate('123CDR123','CDR','123') 123123123Teradata trim function Examples:
select trim (' 1234567891 '); Trim(BOTH FROM ' 1234567891 ') 1234567891Tags: Teradata
Sours: https://dwgeek.com/teradata-string-functions-examples.html/14.8. String Functions and Operators
String Operators
The operator performs concatenation.
String Functions
Note
These functions assume that the input strings contain valid UTF-8 encoded Unicode code points. There are no explicit checks for valid UTF-8 and the functions may return incorrect results on invalid UTF-8. Invalid UTF-8 data can be corrected with .
Additionally, the functions operate on Unicode code points and not user visible characters (or grapheme clusters). Some languages combine multiple code points into a single user-perceived character, the basic unit of a writing system for a language, but the functions will treat each code point as a separate unit.
The and functions do not perform locale-sensitive, context-sensitive, or one-to-many mappings required for some languages. Specifically, this will return incorrect results for Lithuanian, Turkish and Azeri.
- (n) → varchar
Returns the Unicode code point as a single character string.
- (string1, ..., stringN) → varchar
Returns the concatenation of , , , . This function provides the same functionality as the SQL-standard concatenation operator ().
- (string) → bigint
Returns the length of in characters.
- (string) → varchar
Converts to lowercase.
- (string, size, padstring) → varchar
Left pads to characters with . If is less than the length of , the result is truncated to characters. must not be negative and must be non-empty.
- (string) → varchar
Removes leading whitespace from .
- (string, search) → varchar
Removes all instances of from .
- (string, search, replace) → varchar
Replaces all instances of with in .
- (string) → varchar
Returns with the characters in reverse order.
- (string, size, padstring) → varchar
Right pads to characters with . If is less than the length of , the result is truncated to characters. must not be negative and must be non-empty.
- (string) → varchar
Removes trailing whitespace from .
- (string, delimiter) → array<varchar>
Splits on and returns an array.
- (string, delimiter, limit) → array<varchar>
Splits on and returns an array of size at most . The last element in the array always contain everything left in the . must be a positive number.
- (string, delimiter, index) → varchar
Splits on and returns the field . Field indexes start with . If the index is larger than than the number of fields, then null is returned.
- (string, entryDelimiter, keyValueDelimiter) → map<varchar, varchar>
Splits by and and returns a map. splits into key-value pairs. splits each pair into key and value.
- (string, substring) → bigint
Returns the starting position of the first instance of in . Positions start with . If not found, is returned.
- (substring IN string) → bigint
Returns the starting position of the first instance of in . Positions start with . If not found, is returned.
- (string, start) → varchar
Returns the rest of from the starting position . Positions start with . A negative starting position is interpreted as being relative to the end of the string.
- (string, start, length) → varchar
Returns a substring from of length from the starting position . Positions start with . A negative starting position is interpreted as being relative to the end of the string.
- (string) → varchar
Removes leading and trailing whitespace from .
- (string) → varchar
Converts to uppercase.
Unicode Functions
- (string) → varchar
Transforms with NFC normalization form.
- (string, form) → varchar
Transforms with the specified normalization form. must be be one of the following keywords:
Form Description Canonical Decomposition Canonical Decomposition, followed by Canonical Composition Compatibility Decomposition Compatibility Decomposition, followed by Canonical Composition Note
This SQL-standard function has special syntax and requires specifying as a keyword, not as a string.
- (string) → varbinary
Encodes into a UTF-8 varbinary representation.
- (binary) → varchar
Decodes a UTF-8 encoded string from . Invalid UTF-8 sequences are replaced with the Unicode replacement character .
- (binary, replace) → varchar
Decodes a UTF-8 encoded string from . Invalid UTF-8 sequences are replaced with replace. The replacement string replace must either be a single character or empty (in which case invalid characters are removed).
Teradata String Functions
Teradata supports several string functions to manipulate the string.
UPPER & LOWER Function
The UPPER and LOWER functions covert the character column values all in uppercase and lowercase respectively. UPPER and LOWER are ANSI compliant.
Syntax
UPPER ( expression ) –> returns expression as uppercase
LOWER ( expression ) –> returns expression as lowercase
Example
The following example will convert the string “teradatapoint” into upper case.
SELECT UPPER('teradatapoint'); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. Upper('teradatapoint') ---------------------- TERADATAPOINTThe following example will convert the string “TERADATAPOINT” to lower case.
SELECT LOWER('TERADATAPOINT'); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. Lower('TERADATAPOINT') ---------------------- teradatapoint
CHARACTER_LENGTH Function
The CHARACTER_LENGTH function returns the numbers of characters of character string expression.
- The result will be a integer number that represent the length.
- The result will be same for fixed length character.
- Result will vary for variable length character.
- Spaces are valid character so length will be counted for space.
Syntax
CHARACTER_LENGTH ( expression )
Example
The following example will return the number of characters of the string “TERADATAPOINT”.
SELECT CHARACTER_LENGTH('TERADATAPOINT'); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. Characters('TERADATAPOINT') --------------------------- 13
TRIM Function
The TRIM function is used to remove space a particular set of leading or trailing or both from a expression. By default it removes space from both. TRIM is ANSI standard.
Syntax
TRIM ( [ LEADING | BOTH | TRAILING ] [ trim_character] FROM expression)
Example
The following example remove the space from the both end of the string.
SELECT TRIM('TERADATAPOINT'); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. Trim(BOTH FROM 'TERADATAPOINT') ------------------------------- TERADATAPOINT
POSITION Function
The POSITION function is used to return the position of one string inside another. Only the position of first occurrence of the string is returned.
Syntax
POSITION ( expression1 IN expression2 )
Example
The following example will return the first occurrence of point in the string “teradatapoint”.
SELECT POSITION('POINT' IN 'TERADATAPOINT'); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. Position('POINT' in 'TERADATAPOINT') ------------------------------------ 9
SUBSTRING Function
The SUBSTRING is used to positionally extract text from another data value. SUBSTRING is ANSI standard.
Syntax
SUBSTRING ( expression1 FROM n1 [ for n2 ] )
It returns a substring of expression1, starting at the position n1, for a length of n2(if present) or to the end of the string(if not present).
Example
The following example returns the character from 5th position for 4 characters.
SELECT SUBSTRING('TERADATAPOINT' FROM 5 FOR 4); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. Substring('TERADATAPOINT' From 5 For 4) --------------------------------------- DATA
Functions teradata sql string
15.00 - String Function Overview - Teradata Database
String Function Overview
SQL provides a concatenation operator and string functions to translate, concatenate, and perform other operations on strings.
The functions documented in this chapter are designed primarily to work with strings of characters. Because many of them can also process byte and numeric literal and literal data strings, the term string is frequently used here to refer to all three of these data type families.
Data Types on Which String Functions can Operate
The following table lists all the data types that can be processed as strings. Note that not all types are acceptable to all functions. See the individual functions for the types they can process.
Data Type Grouping | ||
Character | Byte | Numeric |
ANSI Equivalence of Teradata SQL String Functions
Several of the Teradata SQL string functions are extensions to the ANSI SQL:2011 standard.
To maintain ANSI compatibility, use the ANSI equivalent functions instead of Teradata SQL string functions, when available.
Change this Teradata string function … | To this ANSI string function in new applications … |
INDEX | POSITION |
MINDEX† | |
SUBSTR | SUBSTRING |
MSUBSTR† | |
† These functions are no longer documented because their use is deprecated and they will no longer be supported after support for KANJI1 is dropped. | |
The following Teradata functions have no ANSI equivalents:
Additional Functions That Operate on Strings
SQL provides other string functions and operators that are not discussed in this chapter.
Teradata String Manipulation
next →← prev
Teradata provides several functions to manipulate the strings. These functions are compatible with the ANSI standard.
Teradata String Functions are also supported most of the standard string functions along with the Teradata extension to those functions.
| String function | Explanation |
|---|---|
| Concat (string1, ..., stringN) | It returns the concatenation of two or more string values. This function provides the same functionality as the SQL-standard concatenation operator (||). |
| Length (string) | It returns the number of characters in the string. |
| Lower (string) | It converts a string to lower case. |
| Upper (string) | It converts a string to upper case. |
| Lpad (string, size, padstring) | Pads the left side of the string with characters to create a new string. |
| Rpad (string, size, padstring) | Pads the right side of the string with characters to create a new string. |
| Trim (string) | It removes leading and trailing whitespace from the given string. |
| Ltrim (string) | It removes leading whitespaces from the string. |
| Rtrim (string) | It removes trailing whitespaces from the string. |
| Replace (string, search) | It removes the search string from the given string. |
| Replace (string, search, replace) | It replaces all instances of search with replacing string. |
| Reverse (string) | It returns string characters in reverse order. |
| Split (string, delimiter) | Split given string on the delimiter. This function returns an array of string. |
| Strops (string, substring) | It returns the staring position first instance of a substring in a given string. |
| Position (substring IN string) | It returns the staring position first instance of a substring in a given string. |
| Substr (string, start, length) | It returns a substring of string that begins at the start position and is length characters long. |
| Chr (n) | It returns the character with the specified ASCII value. |
| to_utf8 (string) | It encodes a string into a UTF-8 varbinary representation. |
| from_utf8 (binary) | It decodes a UTF-8 encoded string from binary. |
| Select translate (string, from, to); | It can replace any character in the string that matches a character in the form set with the corresponding character in the set. |
| Index (string) | It locates the position of a character in a string (Teradata extension). |
UPPER & LOWER Function
The UPPER and LOWER functions convert the character column values all in uppercase and lowercase, respectively. UPPER and LOWER are ANSI compliant.
Syntax
Example
The following example will convert the "Robert" string in the upper case string.
After executing the above code, it will give the following output.
Now in the same example, we will convert the same "ROBERT" string in the lower case string.
Output
CHARACTER_LENGTH Function
The CHARACTER_LENGTH function returns the numbers of characters of a character string expression.
- The result will be an integer number that represents the length.
- The result will be the same for the fixed-length character.
- The result will vary for the variable-length character.
- Spaces are valid characters so that the length will be counted for space.
Syntax
Example
The following example will return the number of characters of the "Robert" string.
Execute the above code, and it tells the length of the "Robert" string as the output shown below.
TRIM Function
The TRIM function is used to remove space a particular set of leading or trailing or both from an expression. TRIM is ANSI standards.
Syntax
Example
The following example removes the space from both the end of the "Robert" string.
When we execute the above code, it will trim the existing space from both ends of the string and gives the following output.
POSITION Function
The POSITION function is used to return the position of a substring inside the string. The position of the first occurrence of the string is returned only.
Syntax
Example
The following example will return the occurrence of "e" in the "Robert" string.
After executing the above code, it will find the position of "e" substring in the "Robert" string as output.
SUBSTRING Function
The SUBSTRING function is used to return a specified number of characters from a particular position of a given string. SUBSTRING function is ANSI standard.
Syntax
It returns a string (str), starting at the position (pos), and length (len) in characters.
Example
The following example returns the character from the 1st position for 3 characters.
The above code returns 3 characters from the 1st position of the string "Robert" as the output.
Next TopicTeradata Date/Time Functions
← prevnext →
Similar news:
- Bosch compact drill 18v
- I^16 simplify
- Kwikset storm door handle
- Ebay walking dead comics
- Disney crib bedding
- Planets in 1st house
- Fun pvp minecraft server
- Minecraft dungeons drop rates
- Anime texture pack mcpe
- 555 bbc rotating assembly
- Cheap apartments in waterloo
- Walmart stock quote
Teradata - INDEX Function - Get Position of Substring in String
INDEX function returns the position (integer number) of a substring in a string.
Quick Example:
Find position of word York in string New York:
SELECTINDEX('New York','York'); -- Result: 5INDEX Overview
Summary information:
| Syntax | INDEX(string, substring) | ||
| When Not Found | Returns 0 when substring is not found in string | ||
| Returns NULL | If string or substring is NULL | ||
| Alternatives | POSITION function | ANSI compliant | Different order of parameters  |
Related Functions:
| POSITION | Get Position of Substring in String | ANSI compliant | Different order of parameters |
Last Update: Teradata 13.0
Teradata INDEX in Other Databases
Defining the position of a substring in other databases:
Oracle:
| INSTR(string, substring [,position [, occurrence]]) | Allows specifying the start position for search and occurrence |