Summary of SQL string operations

Source: Internet
Author: User
Tags first string value of pi

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.