SQL string processing function Summary

In a select statement, only SQL functions can be used to operate fields (link to sqlserver). select field 1from table 1where Field 1. indexOf (cloud) 1; this statement is incorrect because the indexof () function is not an SQL function. You can change it to the corresponding SQL function. Left () is an SQL function. Select field 1from Table 1 wherechar

In a select statement, only SQL functions can be used to operate fields (link to SQL server). select Field 1 from table 1 where field 1. indexOf (cloud) = 1; this statement is incorrect because the indexof () function is not an SQL function. You can change it to the corresponding SQL function. Left () is an SQL function. Select Field 1 from table 1 wherechar

In a select statement, only SQL functions can be used to operate fields (link to SQL server ),

Select Field 1 from table 1 where field 1. IndexOf ("Cloud") = 1;

This statement is incorrect because the indexof () function is not an SQL function. You can change it to the corresponding SQL function.

Left () is an SQL function.

Select Field 1 from table 1 wherecharindex ('cloud', Field 1) = 1;

String functions perform different operations on binary data, strings, and expressions. This type of function applies to CHAR, VARCHAR, BINARY, and VARBINARY data types, and data types that can be implicitly converted to CHAR or VARCHAR. You can use string functions in the SELECT and WHERE clauses and expressions of SELECT statements.

Common string functions include:

I. character conversion functions

1. ASCII ()

Returns the ASCII value of the leftmost character of a character expression. In the ASCII () function, strings with numbers are not enclosed by '', But strings with other characters must be enclosed by''. Otherwise, an error occurs.

2. CHAR ()

Converts an ASCII code to a character. If no value is entered ~ ASCII code value between 255, CHAR () returns NULL.

3. LOWER () and UPPER ()

LOWER () converts all strings to lowercase letters, and UPPER () converts all strings to uppercase letters.

4. STR ()

Converts numeric data to numeric data.

STR ( [, Length [, ])

Length indicates the length of the returned string, and decimal indicates the number of decimal places returned. If no length is specified, the default length is 10 and the default value of decimal is 0.

If length or decimal is a negative value, NULL is returned;

When length is less than the number of digits to the left of the decimal point (including the symbol bit), return length *;

Obey length first, and then take decimal;

When the number of digits of the returned string is less than length, spaces are filled on the left.

Ii. Space-removing Functions

1. LTRIM () removes spaces in the string header.

2. RTRIM () removes spaces at the end of the string.

Iii. substring Functions

1. left ()

LEFT ( , )

Returns character_expression, which starts from integer_expression at the left.

2. RIGHT ()

RIGHT ( , )

Returns character_expression, which is a string of characters starting from integer_expression.

3. SUBSTRING ()

SUBSTRING ( , , Length)

Returns the length of the string starting from the start _ position Character on the left.

Iv. String comparison functions

1. CHARINDEX ()

Returns the starting position of a specified substring in a string.

CHARINDEX (<'substring _ expression'>, )

Here, substring _ expression is the character expression to be searched, and expression can be a string or a column name expression. If no substring is found, the return value is 0.

This function cannot be used for TEXT and IMAGE data types.

2. PATINDEX ()

Returns the starting position of a specified substring in a string.

PATINDEX (<'% substring _ expression %'>, ) The neutron string expression must have a percent sign "%" before and after it; otherwise, the return value is 0.

Unlike the CHARINDEX function, wildcards can be used in the child strings of the PATINDEX function, which can be used for CHAR, VARCHAR, and TEXT data types.

V. String operation functions

1. QUOTENAME ()

Returns a string enclosed by a specific character.

QUOTENAME (<'character _ expression'> [, quote _ character]). quote _ character indicates the character used to enclose the string. The default value is "[]".

2. REPLICATE ()

Returns a character string that repeats character_expression for a specified number of times.

REPLICATE (character_expression integer_expression) if the value of integer_expression is negative, NULL is returned.

3. REVERSE ()

Sorts the characters of a specified string in reverse order.

REVERSE ( ) Character_expression can be a string, a constant, or a column value.

4. REPLACE ()

Returns the string with the specified substring replaced.

REPLACE ( , , ) Use string_expression3 to replace string_expression2 in string_expression1.

4. SPACE ()

Returns a blank string with a specified length.

SPACE ( ) If the value of integer_expression is negative, NULL is returned.

5. STUFF ()

Replace the specified position and length of the string with another substring.

STUFF ( , , , )

If the start position is negative or the length value is negative, or the start position is greater than the length of character_expression1, the return value is NULL.

If the length is greater than the right length of start _ position in character_expression1, character_expression1 retains only the first character.

Vi. Data type conversion functions

1. CAST ()

CAST ( AS [Length])

2. CONVERT ()

CONVERT ( [Length], [, Style])

1) data_type is the data type defined by the SQLServer system. Custom Data Types cannot be used here.

2) length is used to specify the data length. The default value is 30.

3) convert the CHAR or VARCHAR type to an INTEGER type such as INT or SAMLLINT. The result must be a value with a positive or negative number.

4) The conversion from TEXT to CHAR or VARCHAR is a maximum of 8000 characters, that is, the CHAR or VARCHAR data type is of the maximum length.

5) data stored in the IMAGE type is converted to the BINARY or VARBINARY type, up to 8000 characters.

6) convert the integer to the MONEY or SMALLMONEY type and process it according to the currency unit of the defined country, such as RMB, USD, and pound.

7) the BIT type conversion converts a non-zero value to 1 and is still stored as a BIT.

8) if you try to convert the data type to different lengths, the converted value will be truncated and "+" will be displayed after the converted value to identify the occurrence of this truncation.