Useful SQL Summary-reprint

SQL code

1: replace function the first parameter is your string, the second parameter is what you want to replace, and the third parameter is what you want to replace select Replace ('lihan', 'A ', 'B') ------------------------------- lihbn (the number of affected rows is 1) ========================================================== =========================== 2: the first parameter of the substring function is your string, the second is the start replacement position, and the third is the end replacement position select substring ('lihan ); ----- Li (the number of affected rows is 1) ========================================================== =========================== 3: the first parameter of the charindex function is the char to be searched for, and the second parameter is the string to be searched for. Return parameter 1 at the position of parameter 2 select char Index ('A', 'lihan') ----------- 4 (the number of affected rows is 1) ========================================================== =============================== 4: the ASCII function returns the ASCII code value of the leftmost character in the character expression. Select ASCII ('lihan') ----------- 108 (the number of affected rows is 1) ========================================================== ======================================= 5: the nchar function returns Unicode characters with the specified integer code according to the definition of the Unicode standard. The parameter is a positive integer between 0 and 65535. If a value out of this range is specified, null is returned. Select nchar (3213) ---- UNICODE character (the number of affected rows is 1) ========================================================== ========================= 6: soundex returns a four-character code (soundex) used to evaluate the similarity between two strings. Select soundex ('lihan'), soundex ('lihon'); ----- l546 l542 (the number of affected rows is 1) ========================================================== ========================= 7: the Char parameter is an integer between 0 and 255. If this integer expression is not in this range, null is returned. Select char (125) ----} (the number of affected rows is 1) ========================================================== ========================== 8: the first parameter of the STR function must be a number. The second parameter indicates the position converted to the char type. If it is less than the first parameter, * is returned. If it is greater than the right-aligned select STR (12345, 3) ---- *** (the number of affected rows is 1) Select STR (12345, 12) ------------ 12345 (the number of affected rows is 1) ========================================================== =============================== 9: the difference function returns an integer indicating the difference between the soundex values of two character expressions. The returned integer is the number of identical characters in the soundex value. The returned values range from 0 to 4. 0 indicates that the values are almost different or completely different. 4 indicates that the values are almost identical or identical. Select difference ('lihan', 'liha') ----------- 3 (the number of affected rows is 1) ========================================================== =====================================10: the stuff function (four parameters) inserts a string into another string. It deletes characters of the specified length from the start position in the first string, and inserts the second string into the start position of the first string. Select stuff ('lihan', 2, 3, 'lihan') -------- llihann (the number of affected rows is 1) ========================================================== =====================================11: the left function returns n to the leftmost characters. The parameter determines select left ('lihan', 4) ----- liha (the number of affected rows is 1) ========================================================== ====================================== 12 The Right function returns n characters at the rightmost of the string, determined by the parameter select right ('lihan ', 4) ----- IHAN (the number of affected rows is 1) ========================================================== =====================================13: replicate function I think it is to replicate ('lihan', 4) -------------------- lihanlihanlihanlihan (the number of affected rows is 1) ========================================================== =====================================14: len function returns the parameter length select Len ('lihan') ----------- 5 (the number of affected rows is 1) ========================================================== =====================================15: the reverse function reverses the string select reverse ('lihan') ----- nahil (the number of affected rows is 1) ========================================================== =======================================16: the values of the lower and upper functions are case-insensitive. Select lower (upper ('lihan') ---------------- lihan (the number of affected rows is 1) ========================================================== =======================================17: ltrim and rtrim functions Delete left and right spaces select ltrim ('lihan') -------------------------- lihan (the number of affected rows is 1) Select rtrim ('lihan ') --------- lihan (the number of affected rows is 1)