Summary of SQL string operations

Summary of SQL string operations
--to intercept a character from a character in a string, and then insert another string here
Selectstuff (' hello,world! ', 4, 4, ' * * * * ')--return value hel****orld!

--Returns a string of the specified length starting at the specified position
Selectsubstring (' hello,world! ', 2,10)--return value Ello,world

--Replace a character in a string with the specified string
Select replace (' hello,world! ', ' ll ', ' AA ')--return value heaao,world!

--Remove the left space in the string
Select LTrim (' hello,world! ') --Return value hello,world!

--Remove the left space in the string
Selectltrim (' hello,world! ')--return value hello,world!

--Remove the left and right spaces in the string
Selectltrim (' hello,world! ')--return value hello,world!

--Replace the null value with the specified character
Selectisnull (' A ', null)--return value A

--Convert data type
Select cast (' 2007-10-11 ' asdatetime)--return value 2007-10-11 00:00:00.000
Selectconvert (datetime, ' 2007-10-11 ')--return value 2007-10-11 00:00:00.000

--Get string length
Selectlen (' hello,world! ') --Return value 12

--Get the first 3 characters of a string
Selectleft (' hello,world! ', 3)--return value Hel

--Get the following 3 characters of a string
Selectright (' hello,world! ', 3)--return value ld!

--Remove the first 3 characters of a string
Select Right (' hello,world! ', (Len (' hello,world! ') -3))--Return value lo,world!

--Remove the second 3 characters of the string
Select Left (' hello,world! ', (Len (' hello,world! ') -3))--Return value Hello,wor

--Gets the position of a string in the string (returns a number)
Selectcharindex (' e ', ' hello,world! ') --Return value 2

--Returns the first 4 characters from the beginning of the 2nd character
Select Left (' [haha]aaa ', len (' [haha]aaa ')-1), 4)--return value haha haha

--Returns the lowercase form of the character
Selectlower (' hello,world! ') --Return value hello,world!

--Returns the uppercase form of the character
Selectupper (' hello,world! ') --Return value hello,world!

--Replaces all occurrences of the second specified string expression in the first string expression with a third expression
(returns nvarchar if one of the input parameters belongs to the nvarchar data type; otherwise, varchar is returned.) If either parameter is NULL, NULL is returned. ）
Selectreplace (' hello,world! ', ' l ', ' a ')--return value heaao,worad!
Selectreplace (' hello,world! ', ' l ', ')--return value heo,word!
Selectreplace (' hello,world! ', ' l ', null)--return value NULL

--Copy the character expression with the number of arguments on the right
Selectreplicate (' hello,world! ', 4)--return value hello,world! hello,world! hello,world! hello,world!

--Returns the inverted string
Selectreverse (' hello,world! ') --Return value!dlrow,olleh

--When using difference, the more similar two strings are pronounced (English characters only), the larger the return value (the return value is 0-4)
Difference (' Sun ', ' san ')--return value 4
Difference (' Sun ', ' safdsdf ')--return value 3
Difference (' Sun ', ' DGFFGFDG ')--return value 0

--Converts a numeric type with a decimal point to a rounded string that can set the length to set the decimal digit
SELECT STR (123.34584, 7,3)--return value 123.346
--When the set length value is less than the integer part length, the string will return a set length of *
SELECT STR (123333.34584, 5,4)--Return value * * * *

--===================================================================================

--===================================== Digital Operations Summary ==================================

--Returns the largest integer of the specified number
Selectfloor (123456.1234)--return value 123456

--Returns the smallest number without a decimal part and not less than the value of its argument. If the argument is an empty sequence, an empty sequence is returned
Selectceiling (123.010)--Return 124
Selectceiling (NULL)--Returns null

--Returns the value closest to the value after rounding
Selectround (126.018,2)--Return 126.12

--Returns a random number of type float between 0-1
Selectrand ()--Return 0.94170703697981

--Returns the value of pi
SELECTPI ()--Return 3.14159265358979
-------------------Reprint

Summary of SQL string operations