Summary of SQL string processing functions

Only SQL functions can be used in a SELECT statement to manipulate fields (linked SQL Server).

Select field 1 from Table 1 where field 1. IndexOf ("cloud") = 1;

The reason this statement is not correct is that the indexof () function is not a SQL function, and it can be changed to a SQL corresponding function.

Left () is a 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 acts on 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 of a SELECT statement and in expressions.

The common string functions are:

One, character conversion function

1, ASCII ()

Returns the ASCII-code value of the leftmost character of a character expression. In the ASCII () function, a string of pure digits is not "enclosed", but a string containing other characters must be used in a ' surround, or there will be an error.

2, CHAR ()

Converts an ASCII code to a character. If you do not enter an ASCII value between 0 ~ 255, CHAR () returns NULL.

3, LOWER () and Upper ()

LOWER () Converts the string to lowercase, UPPER () to all uppercase.

4, STR ()

Converts numeric data to character data.

STR (<float_expression>[,length[, <decimal>])

length specifies the size of the returned string, and decimal specifies the number of decimal places to return. If you do not specify a length, the default value of length is ten, and the decimal default is 0.

Returns null when length or decimal is negative;

Returns length * When length is less than the number of digits to the left of the decimal point (including the sign bit);

Obey length first, then take decimal;

When the number of string digits returned is less than length, the left complements the space.

Two, go to the space function

1, LTRIM () The string head of the space removed.

2, RTRIM () The end of the string to remove the space.

Three, take the SUBSTRING function

1, left ()

Left (<character_expression>,<integer_expression>)

Returns the character_expression left integer_expression character.

2, right ()

Right (<character_expression>,<integer_expression>)

Returns the character_expression right integer_expression character.

3, SUBSTRING ()

SUBSTRING (<expression>,<starting_ position>,length)

Returns the portion of Fu Qi length characters from the left-starting_ position character of the string.

Four, string comparison function

1, CHARINDEX ()

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

CHARINDEX (< ' substring_expression ', <expression>)

Where substring _expression is the character expression to look for, expression can be a string or a column-name expression. If no substring is found, a value of 0 is returned.

This function cannot be used for text and image data types.

2, PATINDEX ()

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

PATINDEX (< '%substring _expression% ', <column_ name>) The substring expression must be preceded by a percent semicolon "%" or the return value is 0.

Unlike the CHARINDEX function, wildcard characters can be used in substrings of patindex functions, and this function can be used for char, VARCHAR, and text data types.

V. String manipulation functions

1, QuoteName ()

Returns a string that is enclosed by a particular character.

QuoteName (< ' character_expression ' >[, quote_ character]) where Quote_ character the character used to enclose the string, the default value is [].

2, REPLICATE ()

Returns a string that repeats the specified number of character_expression.

REPLICATE (character_expression integer_expression) returns null if the Integer_expression value is negative.

3, REVERSE ()

Reverses the character order of the specified string.

REVERSE (<character_expression>) where character_expression can be a string, a constant, or a value of a column.

4, REPLACE ()

Returns a string that was replaced with the specified substring.

Replace (<string_expression1>,<string_expression2>,<string_expression3>) with String_ Expression3 replaces the substring string_expression2 in the string_expression1.

4, Space ()

Returns a blank string with the specified length.

Space (<integer_expression>) returns null if the Integer_expression value is negative.

5, STUFF ()

Replaces a substring of a string at a specified position, length, with another substring.

STUFF (<character_expression1>,<start_ position>,<length>,<character_expression2>)

Returns a null value if the starting position is negative or the length value is minus, or the starting position is greater than the length of the Character_expression1.

If length length is greater than start_ position in Character_expression1, Character_expression1 retains only the first character.

Vi. data type Conversion functions

1, CAST ()

CAST (<expression> as <data_ type>[length])

2, CONVERT ()

CONVERT (<data_ type>[length],<expression> [, style])

1 data_type data types defined for SQL Server systems, user-defined data types cannot be used here.

2 length is used to specify how long the data is, and the default value is 30.

3 converts a char or varchar type to an integer type such as int or samllint, and the result must be a value with a plus or minus sign.

4 text type to char or varchar type converts up to 8,000 characters, that is, the char or varchar data type is the maximum length.

5 The data stored by the image type is converted to a binary or varbinary type, up to 8,000 characters.

6 to convert the integer value to Money or smallmoney type, according to the definition of the country's currency units to deal with, such as the renminbi, U.S. dollars, sterling and so on.

7 The bit type conversion converts a non-0 value to 1 and is still stored as a bit type.

8 An attempt to convert to a different length of data type, truncates the converted value and displays "+" after the conversion value to identify the truncation.

9 Use the CONVERT () function's style option to display the date and time in a different format. Style is the conversion style number provided by the SQL Server system when converting Datatime and smalldatetime data to strings, and different style numbers have different output formats.

Seven, date function

1, Day (date_expression)

Returns the date value in Date_expression

2, Month (date_expression)

Returns the month value in Date_expression

3, Year (date_expression)

Returns the year value in Date_expression

4, DATEADD ()

DATEADD (<DATEPART>,<NUMBER> <date>)

Returns a new date that is generated by the specified date plus the specified extra date interval.

5, DATEDIFF ()

DATEDIFF (<DATEPART>,<DATE1> <date2>)

Returns the difference in datepart between the two specified dates, that is, the date2 exceeds the date1 gap value, and the resulting value is an integer value with a positive sign.

6, Datename ()

Datename (<datepart>,<date>)

Returns this part of the specified part of the date as a string. specified by DatePart.

7, DATEPART ()

DATEPART (<datepart>,<date>)

Returns the specified portion of a date as an integer value. This section is specified by DatePart.

DATEPART (dd, date) is equivalent to day (date)

DATEPART (mm, date) is equivalent to month (date)

DATEPART (yy, date) is equivalent to year (date)

8, GETDATE ()

Returns the current date and time of the system in the default format of DateTime.