sql-Math, String, time-date functions, and type conversions

Source: Internet
Author: User
Tags abs mathematical functions rtrim time and date truncated

--Mathematical functions
--abs Absolute Value,
Select ABS ( -99)
--ceiling to take the upper limit,
Select CEILING (4.5)
--floor to the lower limit
Select Floor (4.5)
--power several parties,
Select POWER (2,2)
--round rounded,
Select Round (6.45,1)
--sqrt Open Square
Select SQRT (9)
--square Square
Select SQUARE (5)

--String function
--ascii returns the leftmost character of the string ASCII code
Select ASCII (' name ')
Select ASCII (name) from Student1--View the ASCII code for the first character of the owner's name
--char convert ASCII code to characters
Select CHAR (.)
Select CHAR (Chinese) from Student1--speak all language fractions converted to characters
--note that the expression or constant that is converted (integer) needs to be between 0-256 and out of the output n

--len returns the length of a string
Select LEN (' Asdfghh ')
Select LEN (name) from Student1 --Displays the length of all names

--charindex returns the index of the first character of a string that appears at the beginning of a string
Select CHARINDEX (' d ', ' ASDFGHHJKKHG ')--index starting from 1
Select CHARINDEX (' All ', age) from Kaoshi --View the index that appears in 23

--difference return similarity degree with 0--4
Select difference (' ASDDFGHJK ', ' adfjkgh ')
--left means the string is truncated from the left
Select Left (' Asfgdsssdgh ', 4)

--right from the right.
Select Right (' Asfgdsssdgh ', 4)

--lower all converted to lowercase

--upper Caps
Select UPPER (' Afadsfasfsdfssad ')

--ltrim Remove the left space
Select LTRIM (' ASD ')
--rtrim Remove the right space
Select RTRIM (' ASD ')

--patindex equivalent to the first character index bit in the character where the charindex returned the string
Select PATINDEX ('%dasda% ', ' 154dasda546 ')

--replace Find replacement
select REPLACE (Sex, ' female ', ' girl ') from Student1--display only, do not change
--replicat copy and paste
select REPLICATE (' ASD ', 3)--altogether three times
--reverse Flip

--space spaces
Select ' A ' +space (5) + ' BC '

--str cast to String
Select STR (123456.222,5,1)--Parameter 1 is the value to be converted, and parameter 2 is the length retained after conversion
--Parameter 3 is the number of digits to be retained after the decimal point
--Note that parameter 2 cannot be converted when it is less than the integer portion of the parameter 1

--from the position of the first few indexes, See if you need to delete a few backwards, and then insert what you want to insert into the
--parameter 1 is the string that needs to be inserted
--Parameter 2 is the beginning of the first few indexes

--Parameter 4 is the newly inserted character

--intercept string
--Parameter 1 is the truncated string
--the index from which the parameter 2 starts
--Parameter 3 is the length of the Intercept
Select SUBSTRING (' 151111111111111111111111888861 ', 1,4)

--1. Time-Date function:
Set Datefirst 1 --setting Monday as the first day
--datepart function to return a part of a time date
--Parameter 1 refers to the return of the part
--Parameter 2 means returning from that time date
--datefirst is a system constant that needs to be added with @@
SELECT @ @DATEFIRST as ' 1st day ', DATEPART (Dw,getdate ()) as ' Tobay '
Select GETDATE ()--execution is after system time
-the type that needs to be changed , the quantity that needs to be changed, the time and date to change

Select DATEADD (hh,5, ' 2015-12-12 ')
--datediff Calculate the time difference, different different, difference
--the type that needs to be changed, the start date, the end time date
Select DateDiff (MM, ' 2013-12-31 ', ' 2014-1-3 ')
DECLARE @startday varchar--you need to add a data type when declaring a variable.
Set @startday = ' 1993-11-1 '--Sets the value of the variable
Select DATEDIFF (Day, @startday, GETDATE ())--as ' online time '
Select Day (' 1993-11-1 ')
Select MONTH (' 1993-11-1 ')
Select year (' 1993-11-1 ')
--datepart Returns a portion of a time date
--Parameter 1 is which part of the return
--the date on which the parameter 2 is calculated
Select DATEPART (year, ' 2009-9-8 ')--Return years
Select DATEPART (DY, ' 2009-9-8 ')--the first day of the year to return to DayOfYear
Select DATEPART (QQ, ' 2009-9-8 ')--Return to quarterly total of four quarters
--Determine if the date time is correct
Select ISDATE (' 2012-2-29 ')--Returns correctly 1
Select ISDATE (' 2011-2-29 ')--error returned 0
--2. Type conversions:

--Data type conversion cast convert
--cast writes the converted value + as + the type converted to
Select CAST (1.999 as int)
Select CAST (1.56656 as varchar )
Select CAST (1.3996646 as Decimal (18,2))
Select cast (CAST (' 1.5656 ' as Decimal (18,2) as int)
Select CAST (' 1.5656 ' as Decimal (18,2))
The --convert parameter 1 is the type that needs to be converted, and parameter 2 is the value that needs to be converted
Select CONVERT (int, ' a ')
Select CONVERT (Decimal (18,2), ' 89.9912 ')--automatically rounded when the exact number of digits is later
Select CONVERT (varchar (), 3.1415926)

sql-Math, String, time-date functions, and type conversions

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.