Examples of common SQL Server Functions

-- String Function
-- Substring
Print substring ('iamagoodperson)
Select substring ('iamoodperson)

-- Upper
Select upper ('he is a good person ')

-- Lower
Select lower ('this is an very interesting job ')

-- Ltrim
Select ltrim ('I am a good person ')

-- Rtrim
Select rtrim ('heihei, I do not know why it likes this ')


-- Replace
Select Replace ('iwanttoaskyou', 'ttoa', 'I love you ')

-- Stuff
Select stuff ('My name is Zhu xujie ', 6, 8, 'summer ')

-- Date/time fuction
-- Getdate ()
Select getdate () as 'today'

-- Dateadd ()
Select dateadd (YY, 10, getdate ())

-- Datediff ()
Select datediff (YY, '2014/1/3', getdate ()

-- Datepart ()
Select datepart (DW, getdate ())
Select datepart (YY, getdate ())
Select datepart (mm, getdate ())
Select datepart (DD, getdate ())
Select datepart (SS, getdate ())
Select datepart (MS, getdate ())
Select datepart (DD, '2014/1/3 ')
Print datepart (DW, '2014/1/22 ')

-- Day (), equivalent to datepart (DD, time)
Select Day ('2014/1/3 ')
Select Day (getdate ())

-- Month (), equivalent to datepart (mm, time)
Select month (getdate ())

-- Year (), equivalent to datepart (YY, time)
Select Year (getdate ())

-- Mathematical functions

-- ABS ()
Select ABS (-1, 100.3456)

-- Sin ()
Select sin (1, 0.54)

-- Cos ()
Select cos (1, 3.14)

-- Power ()
Select power (10, 2)

-- Round returns a number expression rounded to the specified length or precision.


Select round (100.45, 1)
Select round (123,45,-2)

-- Floor ()
Select floor (4.9)
Select floor (-1, 123.99)

-- Ceiling ()
Select ceiling (1, 4.9)
Select ceiling (-1, 123.99)

-- SQRT ()
Select SQRT (1, 100)

-- Square
Select square (10)
Select square (-15)

-- Conversion Function
-- Cast ()
Select cast (100.45 as INT)
Select cast (1345 as varchar (10 ))

-- Convert ()
Select convert (INT, 100.56)
Select convert (varchar (10), 2345)

-- Null Function
-- Isnull ()
Declare @ temp_table table
(
Bookid varchar (10) primary key,
Book_price float default null,
Bookname varchar (50)
)
Insert into @ temp_table values ('1', 50, 'c #')
Insert into @ temp_table values ('2', null, 'C ')
Select bookid as 'book number', isnull (book_price, 0) as 'book price'
From @ temp_table

-- Nullif (). If the two expressions in the parameter are the same, null is returned.
Select nullif ('imam', 'imam ')

-- Coalesce returns the first non-null expression in the parameter.
Select coalesce (null, null, 'I am a good boy') # Database Technology