String Canonical Functions

Entity SQL includes string canonical functions.

Remarks

The following table shows the string Entity SQL canonical functions.

Function	Description
Concat (string1, string2)	Returns a string that contains string2 appended to string1. Arguments string1: The string to which string2 is appended. string2: The string that is appended to string1. Return Value A String. An error will occur if the length of the return value string is greater than the maximum length allowed. Example -- The following example returns abcxyz. Concat('abc', 'xyz')
Contains (string, target)	Returns true if target is contained in string. Arguments string: The string that is searched. target: The target string that is searched for. Return Value true if target is contained in string; otherwise false. Example -- The following example returns true. Contains('abc', 'bc')
EndsWith (string, target)	Returns true if target ends with string. Arguments string: The string that is searched. target: The target string searched for at the end of string. Return Value True if string ends with target; otherwise false. Example -- The following example returns true. EndsWith('abc', 'bc') Note: If you are using the SQL Server data provider, this function returns false if the string is stored in a fixed length string column and target is a constant. In this case, the entire string is searched, including any padding trailing spaces. A possible workaround is to trim the data in the fixed length string, as in the following example: EndsWith(TRIM(string), target)
IndexOf(target, string)	Returns the position of target inside string, or 0 if not found. Returns 1 to indicate the beginning of string. Index numbering starts from 1. Arguments target: The string that is searched for. string: The string that is searched. Return Value An Int32. Example -- The following example returns 4. IndexOf('xyz', 'abcxyz')
Left (string, length)	Returns the first length characters from the left side of string. If the length of string is less than length, the entire string is returned. Arguments string: A String. length: An Int16, Int32, Int64, or Byte. length cannot be less than zero. Return Value A String. Example -- The following example returns abc. Left('abcxyz', 3)
Length (string)	Returns the (Int32) length, in characters, of the string. Arguments string: A String. Return Value An Int32. Example -- The following example returns 6. Legth('abcxyz')
LTrim(string)	Returns string without leading whitespace. Arguments A String. Return Value A String. Example -- The following example returns abc. LTrim(' abc')
Replace (string1, string2, string3)	Returns string1, with all occurrences of string2 replaced by string3. Arguments A String. Return Value A String. Example -- The following example returns abcxyz. Concat('abc', 'xyz')
Reverse (string)	Returns string with the order of the characters reversed. Arguments A String. Return Value A String. Example -- The following example returns dcba. Reverse('abcd')
Right (string, length)	Returns the last length characters from the string. If the length of string is less than length, the entire string is returned. Arguments string: A String. length: An Int16, Int32, Int64, or Byte. length cannot be less than zero. Return Value A String. Example -- The following example returns xyz. Right('abcxyz', 3)
RTrim(string)	Returns string without trailing whitespace. Arguments A String. Return Value A String.
Substring (string, start, length)	Returns the substring of the string starting at position start, with a length of length characters. A start of 1 indicates the first character of the string. Index numbering starts from 1. Arguments string: A String. start: An Int16, Int32, Int64 and Byte. start cannot be less than one. length: An Int16, Int32, Int64 and Byte. length cannot be less than zero. Return Value A String. Example -- The following example returns xyz. Substring('abcxyz', 4, 3)
StartsWith (string, target)	Returns true if string starts with target. Arguments string: The string that is searched. target: The target string searched for at the start of string. Return Value True if string starts with target; otherwise false. Example -- The following example returns true. StartsWith('abc', 'ab')
ToLower(string)	Returns string with uppercase characters converted to lowercase. Arguments A String. Return Value A String. Example -- The following example returns abc. ToLower('ABC')
ToUpper(string)	Returns string with lowercase characters converted to uppercase. Arguments A String. Return Value A String. Example -- The following example returns ABC. ToUpper('abc')
Trim(string)	Returns string without leading and trailing whitespace. Arguments A String. Return Value A String. Example -- The following example returns abc. Trim(' abc ')

These functions will return null if given null input.

Equivalent functionality is available in the Microsoft SQL Client Managed Provider. For more information, see SqlClient for Entity Framework Functions.

See Also

Concepts

Canonical Functions