SQL string basic operation summary

Basic SQL string operations

From: http://hi.baidu.com/15960278709/blog/item/bd3507ddf14af3ea76c63805.html

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

-- Truncates a string from a character and inserts another string here
Select stuff ('hello, world! ', 4, 4,' ***** ') -- Return Value: hel*** orld!

-- Returns a string of the specified length starting from the specified position.
Select substring ('hello, world! ',) -- Return Value: ello, world

-- Replace a character segment in the string with the specified string
Select Replace ('hello, world! ','Ll', 'A') -- Return Value heaao, world!

-- Remove spaces on the left of the string
Select ltrim ('hello, world! ') -- Return Value: Hello, world!

-- Remove spaces on the left of the string
Select ltrim ('hello, world! ') -- Return Value: Hello, world!

-- Remove spaces on the left and right of the string
Select ltrim ('hello, world! ') -- Return Value: Hello, world!

-- Replace null with a specified character
Select isnull ('A', null) -- Return Value

-- Convert data types
Select cast ('1970-10-11 'As datetime) -- Return Value 2007 00:00:00. 000
Select convert (datetime, '2017-10-11 ') -- Return Value 2007 00:00:00. 000

-- Get the string length
Select Len ('hello, world! ') -- Return value 12

-- Obtain the first three characters of a string.
Select left ('hello, world! ', 3) -- Return Value "El"

-- Obtains the last three characters of a string.
Select right ('hello, world! ', 3) -- Return Value lD!

-- Remove the first three characters of the string
Select right ('hello, world! ', (LEN ('hello, world! ')-3) -- returns Lo, world!

-- Remove the last three characters of the string
Select left ('hello, world! ', (LEN ('hello, world! ')-3) -- Return Value: Hello, wor

-- Obtain the position of a string in the string (return number)
Select charindex ('E', 'Hello, world! ') -- Return Value 2

-- Returns the first 4 characters starting from 2nd characters
Select left (right ('[Hahahaha] aaa', Len (' [hahahahaha] aaa')-1), 4) -- Return Value hahahahaha

-- Return lowercase characters
Select lower ('hello, world! ') -- Return Value: Hello, world!

-- Returns uppercase characters.
Select upper ('hello, world! ') -- Return Value: Hello, world!

-- Replace all matching items of the Second specified string expression in the first string expression with the third expression
(If one of the input parameters belongs to the nvarchar data type, nvarchar is returned; otherwise, varchar is returned. If any parameter is null, null is returned .)
Select Replace ('hello, world! ', 'L', 'A') -- Return Value heaao, worad!
Select Replace ('hello, world! ', 'L', '') -- Return Value Heo, word!
Select Replace ('hello, world! ', 'L', null) -- Return Value null

-- Copy the character expression by the number of values in the right Parameter
Select replicate ('hello, world! ', 4) -- Return Value: Hello, world! Hello, world! Hello, world! Hello, world!

-- Returns the reversed string.
Select reverse ('hello, world! ') -- Return value! Dlrow, olleh

-- When difference is used, the closer the two strings are to be pronounced (only English characters are allowed), the larger the return value (the return value is between 0 and 4)
Difference ('sun', 'san') -- Return Value 4
Difference ('sun', 'safdsdf ') -- Return Value 3
Difference ('sun', 'dgffgcd') -- Return Value 0

-- Converts the numeric type with a decimal point to a string with a configurable length that can be rounded to a decimal point.
Select STR (123.34584, 7, 3) -- Return Value 123.346
-- When the length value is smaller than the length of the integer part, the string returns the length of the specified part *
Select STR (123333.34584, 5, 4) -- Return Value *****

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

-- ============================================================ Numeric operation summary ========================================

-- Returns the maximum integer of a specified number.
Select floor (123456.1234) -- Return Value 123456

-- Returns the minimum number without decimal digits and not less than the value of the parameter. If the parameter is an empty sequence, an empty sequence is returned.
Select ceiling (123.010) -- returns 124
Select ceiling (null) -- return null

-- Returns the value closest to this value after rounding.
Select round (126.018, 2) -- returns 126.12

-- Returns a float-type random number between 0 and 1.
Select rand () -- returns 0.94170703697981

-- Returns the PI value of the circumference rate.
Select Pi () -- returns 3.14159265358979

For summary