Complete collection of SQL string processing functions

Time:2022-5-11

In the select statement, only SQL functions can be used to operate fields (link SQL Server),
Select field 1 from table 1 where field 1 Indexof (“cloud”) = 1;
The reason why this statement is incorrect is that the indexof() function is not an SQL function. Just change it to the function corresponding to SQL.
Left () is an SQL function.
Select field 1 from table 1 where CHARINDEX (‘cloud ‘, field 1) = 1;

String functions perform different operations on binary data, strings, and expressions. Such functions act 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 expressions.
Common string functions are:

1、 Character conversion function

1、ASCII()
Returns the ASCII value of the leftmost character of a character expression. In the ASCII () function, the string of pure numbers can not be enclosed by ”, but the string containing other characters must be enclosed by ”, otherwise an error will occur.
2、CHAR()
Converts ASCII code to characters. Char() returns NULL if no ASCII value between 0 and 255 is entered.
3. Lower() and upper()
Lower() converts all strings to lowercase; Upper() converts all strings to uppercase.
4、STR()
Convert numerical data to character data.
STR (<float_expression>[,length[, <decimal>]])
Length specifies the length of the returned string, and decimal specifies the number of decimal places returned. If no length is specified, the default value of length is 10 and the default value of decimal is 0.
When length or decimal is negative, null is returned;
When the length is less than the number of digits to the left of the decimal point (including sign digit), the length * is returned;
First obey length, and then take decimal;
When the number of bits of the returned string is less than length, a space is added on the left.

2、 De whitespace function

1. Ltrim() removes the space at the beginning of the string.

2. Rtrim() removes the space at the end of the string.

3、 Substring function

1、left()
LEFT (<character_expression>, <integer_expression>)
Return character_ Expression integer from left_ Expression characters.

2、RIGHT()
RIGHT (<character_expression>, <integer_expression>)
Return character_ Expression right up integer_ Expression characters.

3、SUBSTRING()
SUBSTRING (<expression>, <starting_ position>, length)
Returns starting from the left of the string_ Position is a length character part.

4、 String comparison function

1、CHARINDEX()
Returns the starting position of a specified substring in a string.
CHARINDEX (<‘substring_expression’>, <expression>)
Substring_ Expression is the character expression to find. 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 with text and image data types.
2、PATINDEX()
Returns the starting position of a specified substring in a string.
Patindex (< ‘% substring _expression%’, < column _name >), where the substring expression must have a percentage sign “%” before and after it, otherwise the return value is 0.
Unlike the CHARINDEX function, wildcards can be used in substrings of the patindex function, and this function can be used for char, varchar, and text data types.

5、 String operation function

1、QUOTENAME()
Returns a string enclosed by a specific character.
Quotename (<‘character_expression ‘> [, quote_character]) where quote_ Character indicates the character used to enclose the string, and the default value is “[]”.
2、REPLICATE()
Returns a duplicate character_ The number of times the expression string is specified.
Replicate (character_expression integer_expression)_ If the expression value is negative, null is returned.

3、REVERSE()
Reverses the character order of the specified string.
Reverse (< character_expression >) where character_ Expression can be a string, a constant, or the value of a column.

4、REPLACE()
Returns a string replaced with a specified substring.
Replace (< string_expression1 >, < string_expression2 >, < string_expression3 >) with string_ Expression3 replaces string_ Substring string in expression1_ expression2。

4、SPACE()
Returns a blank string with a specified length.
Space (< integer_expression >)_ If the expression value is negative, null is returned.

5、STUFF()
Replace the substring of the specified position and length of the string with another substring.
STUFF (<character_expression1>, <start_ position>, <length>,<character_expression2>)
If the start position is negative or the length value is negative, or the start position is greater than character_ The length of expression1, null value is returned.
If length is greater than character_ Start in expression1_ Position is the right length, then character_ Expression1 retains only the first character.

6、 Data type conversion function

1、CAST()
CAST (<expression> AS <data_ type>[ length ])

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

1)data_ Type is the data type defined by the SQL server system. User defined data types cannot be used here.
2) Length is used to specify the length of data. The default value is 30.
3) Convert char or varchar type to integer type such as int or samlink, and the result must be a numeric value with a positive or negative sign.
4) The maximum conversion from text type to char or varchar type is 8000 characters, that is, char or varchar data type is the maximum length.
5) The data stored in image type is converted to binary or varbinary type, with a maximum of 8000 characters.
6) Convert the integer value to money or smallmoney type and process it according to the currency unit of the defined country, such as RMB, US dollar, British pound, etc.
7) Bit type conversion converts a non-zero value to 1 and still stores it as bit type.
8) When trying to convert to data types of different lengths, the conversion value will be truncated and a “+” will be displayed after the conversion value to identify that such truncation has occurred.
9) Use the style option of the convert() function to display the date and time in different formats. Style is the conversion style number provided by the SQL server system when converting DataTime and smalldatetime data into strings. Different style numbers have different output formats.

7、 Date function

1、day(date_expression)
Return date_ Date value in expression

2、month(date_expression)
Return date_ Month value in expression

3、year(date_expression)
Return date_ Year value in expression

4、DATEADD()
DATEADD (<datepart>, <number>, <date>)
Returns the new date generated by the specified date plus the specified additional date interval number.
5、DATEDIFF()
DATEDIFF (<datepart>, <date1>, <date2>)
Returns the difference in datepart between two specified dates, that is, the difference between date2 and date1. The resulting value is an integer value with a sign.

6、DATENAME()
DATENAME (<datepart>, <date>)
Returns the specified portion of the date as a string. Specified by datepart.

7、DATEPART()
DATEPART (<datepart>, <date>)
Returns the specified part of the date as an integer value. This part 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.