String functions
Last updated
Last updated
The string functions covered in this section perform operations on a string (char or varchar) input value and return a string or numeric value.
ASCII (American Standard Code for Information Interchange) returns the ASCII code value of the leftmost character of a character expression.
This function converts an int between 0 to 255 to a character value. Outside of this range, the CHAR function will return a NULL value.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
integer_expression
An integer from 0 through 255.
char(1)
This function searches for one character expression inside another character string. If found, the function will return the starting position of the first expression.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
expressionToFind
The expression that needs to be found in the ExpressionString
expressionString
The string that contains the expression
start_Location
Start location from where the search will start
If CHARINDEX doesn't find expressionToFind within expressionString
, CHARINDEX will return 0.
Returning the starting position of an expression
This example searches for a
in the string value.
Returning the starting position of an expression with an optional start location
This example searches for a
in the string value starting from 15th position.
The CONCAT function concatenates two or more string values one after the other. This function requires at least 2 strings and no more than 254 strings to concatenate.
string
A string to concatenate to the other strings.
string
A string with all the concatenated strings.
This function returns an integer value measuring the difference between the SOUNDEX () values of two different character expressions.strings.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
string
An alphanumeric expression of character data. string can be a constant, variable, or column.
int
Returns a value formatted with the specified format. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
value
Expression of a supported data type to format. For a list of valid types, see the table in the following Remarks section.
format
nvarchar format pattern.
The format argument must contain a valid .NET Framework format string, either as a standard format string (for example, "C" or "D"), or as a pattern of custom characters for dates and numeric values (for example, "MMMM DD, YYYY (DDDD)").
nvarchar or null
The length of the return value is determined by the format.
The following table lists the acceptable data types for the value argument together with their .NET Framework mapping equivalent types.
FORMAT with strings
The following example shows formatting date values by specifying a custom format.
FORMAT with numeric values.
The following example shows formatting numeric values by specifying a custom format.
Returns the left part of a character string with the specified number of characters.
string
Is an expression of character or binary data. It can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert string.
integer
Is a positive integer that specifies how many characters of the string will be returned.
Returns a string
The following example returns the two leftmost characters from the string.
Returns the number of characters of the specified string expression, excluding trailing spaces.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
string
Is the string expression.
bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.
The following example selects the number of characters and the data in string abcde
.
Returns a character expression after converting uppercase character data to lowercase.
string
Is an expression of character or binary data.
varchar or nvarchar
The following example uses the LOWER
function.
Returns a character expression after it removes leading blanks.
string
Is an expression of character or binary data.
varchar or nvarchar
Example: Using LTRIM
The following example uses LTRIM to remove leading spaces from a string
Returns the starting position of the first occurrence of a pattern in a specified expression.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
pattern
Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern.
expression
Is an expression, typically a column that's searched for the specified pattern. expression is of the string data type category.
int or bigint
Example
The following example checks a short character string (this is a great day
) for the starting location of the characters eat
.
Replaces all occurrences of a specified string value with another string value.
string
Is the string expression to be searched.
string_toBeReplaced
Is the string to be found in the string.
string_replacedBy
Is the replacement string.
Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.
The following example replaces the string cde
in abcdefghi
with xyz
.
Returns the reverse order of a string value.
string
It's an expression of a string or binary data type.
varchar or nvarchar
The following example returns the reverse of the sting.
Returns the right part of a character string with the specified number of characters.
string
Is an expression of character or binary data.
integer
Is a positive integer that specifies how many characters of string will be returned.
Returns varchar when character_expression is a non-Unicode character data type.
Returns nvarchar when character_expression is a Unicode character data type.
Using RIGHT with a string
Returns a character string after truncating all trailing spaces.
string
Is an expression of character data.
varchar or nvarchar
The following example takes a string of characters that has spaces at the end of the sentence and returns the text without the spaces at the end of the sentence.
Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
string
Is an alphanumeric expression of character data. SOUNDEX converts an alphanumeric string to a four-character code that's based on how the string sounds when spoken.
varchar
The following example shows the standard SOUNDEX
values are returned for all consonants. Returning the SOUNDEX
for Raul
and Rahul
returns the same SOUNDEX result because all vowels, the letter y
, doubled letters, and the letter h
, aren't included.
Returns a string of repeated spaces.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
integer_expression
Is a positive integer that indicates the number of spaces.
varchar
The following example concatenates a comma, two spaces, and the first name of the person.
Returns character data converted from numeric data. The character data is right-justified, with a specified length and decimal precision.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
float_expression
Is an expression of approximate numeric (float) data type with a decimal point.
length
Is the total length. This includes decimal point, sign, digits, and spaces. The default is 10.
decimal
Is the number of places to the right of the decimal point. decimal must be less than or equal to 16. If decimal is more than 16 then the result is truncated to sixteen places to the right of the decimal point.
varchar
The following example converts an expression that's made up of five digits and a decimal point to a six-position character string. The fractional part of the number is rounded to one decimal place.
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
string
Is an expression of character data.
start
Is an integer value that specifies the location to start deletion and insertion.
length
Is an integer that specifies the number of characters to delete.
replaceWith
Is an expression of character data.
Returns character data if string is one of the supported character data types. Returns binary data if string is one of the supported binary data types.
The following example returns a character string created by deleting three characters from the first string, abcdef
, starting at position 2
, at b
, and inserting the second string at the deletion point.
Returns part of a character, binary, text, or image expression in SQL Server.
expression
Is a character, binary, text, ntext, or image expression.
start
Is an integer or bigint expression that specifies where the returned characters start.
length
Is a positive integer or bigint expression that specifies how many characters of the expression will be returned.
Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types. The returned string is the same type as the specified expression with the exceptions shown in the table.
Example
The following example shows how to return only a part of a character string.
Returns a character expression with lowercase character data converted to uppercase.
string
Is an expression of character data.
varchar or nvarchar
The following example uses the UPPER
function to return the name in uppercase.
Category | Type | .NET type |
---|---|---|