SQL Server advanced Programming T-SQL programming functions

--String function
--1, LEN: Returns the number of characters in a string
Select Len (' China '), Len (' abc123! ')
Select Len (' abc ' + ' 1 '), Len (' abc ')
--2, Datalength: Returns the number of bytes in a string
Select Datalength (' China '), datalength (' abc123! ')
--3, right, left: Returns a portion of the character of a string to the
Select Right (' ABCdef ', 3), left (' abcdef ', 3)
--4, Substring: string intercept function SQL subscript starting from 1
Select SUBSTRING (' ABCdef ', 2, 3)
--5, CharIndex: String lookup function
Select CHARINDEX (' A ', ' BCADADFD ', 1)--' Find what ', ' concrete ', find the first few
--6, UPPER: letter Capitalization conversion function
--7, LoWER: letter Lowercase conversion function
Select UPPER (' abc123 '), LOWER (' ABc123 in! ‘)
--8, Space: Creating a space function
Select Len (' abc ' +space (10) + ' 123 ')
--9, Replicate: string repetition function
Select REPLICATE (' abc ', 3)
--10, replace: Character substitution function
Select replace (' oioo000oooo ', ' o ', ' 0 ')--after two double quotes: what to replace
--11, Stuff: Character substitution function
Select STUFF (' Hunan Wuhan ', 2, 1, ' North ')
Select STUFF (' Changsha, China ', 3, 0, ' Wuhan ')
--12, LTrim, and RTrim: Remove all whitespace from the left or right of the string
Select Len (LTRIM (' abc d ')), LEN (RTRIM (' abc d '))
--13, STR: Converting a value to a string function
Select STR (12345.7654,5,2)--8-bit 2-bit decimal place
--14, Char: Converts an ASCII value to one character
Select CHAR, ASCII (' * ')

--Date function
--1, GETDATE (): Returns the current time on the server
Select GETDATE ()
--2, DatePart: Returns a part of a Date value (integer type)
--3, Datename: Returns a part of a date value (string)
--return the day of the week
Select DATEPART (Dw,getdate ()), Datename (Dw,getdate ())
--Returns the week ordinal of a year
Select DATEPART (Week,getdate ()), Datename (Week,getdate ())
--4, DateDiff: Date comparison function
--Returns how many days each transaction has occurred
Use BANKDB
Select DAtediff (Dd,transdate,getdate ()) from Transinfo
--5, DateADD: Date change function
Select DATEADD (Day,2,getdate ())

---Customize a function to obtain the student's two-subject average score according to the student's name
If object_id (' Avgscore ') is not null
Drop function Avgscore
Go
Create function Avgscore (@name varchar (50))
Returns decimal as
Begin
DECLARE @stuId decimal=0
Select @stuId =stuid from Stuinfo where stuname [email protected]
DECLARE @m decimal=0
Select @m=avg (Writeexam+labexam)/2 from Exam where @stuId =stuid
Return @m
End
Go

SELECT dbo. Avgscore (' Zhang Sanfeng ')

SQL Server advanced Programming T-SQL programming functions