String Canonical Functions (Entity SQL)

Entity SQL includes string canonical functions.

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

A String.

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')

IndexOf( string1, string2)

Returns the position of string1 inside string2, or 0 if not found. Returns 1 to indicate the beginning of string. Index numbering starts from 1.

Arguments

A String.

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 and 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 and 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)

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 .NET Framework Data Provider for SQL Server (SqlClient) for the Entity Framework Functions.

Community Additions

Show: