Common SQL string functions

Q:
Ask an SQL 2000 sever question:
Select * From itemcode
Where code like '20140901'
How to Set code = 40101001
To code = 401-01-001
Currently, 666 codes are similar to 40101001
Which statement can be used to convert it to 401-01-001?

A:
Update itemcode set code = Replace (Code, left (Code, 8), left (Code, 3) + '-' + substring (Code, 4, 2) + '-' + substring (Code, 6, 3) where code like '20140901'

Q:
Replace (Code, left (Code, 8), left (Code, 3) + '-' + substring (Code, 4, 2) + '-' + substring (Code, 6, 3 )) what does that mean?

A:
Use code = 123456789 as an Example
A = left (Code, 8) = 12345678
B = left (Code, 3) = 123
C = substring (Code, 4, 2) = 45
D = substring (Code, 6, 3) = 678
E = B + '-' + C + '-' + E = 123-45-678
F = Replace (Code, a, e) = 123-45-6789
I should be able to understand it with the help below.
1. Left (Code, 8) takes the first 8 digits
2. substring (Code, 4th) uses two digits starting from bits.
3. + is a string connector.
4. Replace (A, str1, str2) replace str1 in a with str2

After a few questions, I want to sort out a complete SQL string function to learn and facilitate future queries.

SQL string functions

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 (<float_expression> [, length [, <decimal>])
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 value 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 (<character_expression>, <integer_expression>)
Returns character_expression, which starts from integer_expression at the left.

2. Right ()
Right (<character_expression>, <integer_expression>)
Returns character_expression, which is a string of characters starting from integer_expression.

3. substring ()
Substring (<expression>, <starting _ Position>, 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' >,< 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 %'>, <column _ name>) must have a percent sign (%) before and after the substring expression; 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>) Where character_expression can be a string, a constant, or a column value.

4. Replace ()
Returns the string with the specified substring replaced.
Replace (<string_expression1>, <string_expression2>, <string_expression3>) Use string_expression3 to replace string_expression2 in string_expression1.

4. Space ()
Returns a blank string with a specified length.
Space (<integer_expression>) 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 (<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 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 (<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. 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.
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 to a string. Different style numbers have different output formats.

VII. Date Functions
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 the new date generated by the specified date plus the specified additional date interval number. The value of "datepart" is as follows:

5. datediff ()
Datediff (<datepart>, <date1>, <date2>)
Returns the difference between two specified dates in datepart, that is, the difference between date2 and date1. The result value is an integer with positive and negative signs.

6. datename ()
Datename (<datepart>, <date>)
Returns the specified part of the date in the form of a string. Specified by datepart.

7. datepart ()
Datepart (<datepart>, <date>)
Returns the specified part of the date in the form of an integer. 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.