SQLServerの共通関数の概要説明

select LEN(name) from test1 -- Calculate the length of name

select lower('STUDENT !')
select upper('STUDENT !')

declare @str varchar(100) = ' a a a a '
select ltrim(@str)
select rtrim(@str)

select substring('HelloWorld!',6,6) -- can intercept any length
select left('HelloWorld!' ,5) ---intercept from the left
select right('HelloWorld!' ,6) ---intercept from the right

select replace('HelloWorld!','o','e') --string, the string to be replaced, the string to replace

select reverse('abc') --cba

charindex(srt1 ,srt2) -- the starting position of srt1 in srt2

select charindex('H','elloHWorld') result is: 5 -- can only check the first occurrence of the position, does not match return 0

select replicate('abc',4) The result is: abcabcabcabc

select avg(id) from test1 avg(column name)

select min(id) from test1
select max(id) from test1

select sum(id) from test1

select count(id) from test1

Count and sort students' total scores
select stu_id as student number ,name as student name , SUM(Language+English+Math+Algebra) as total scorefrom tb_stuAchievement 
ORDER BY total score DESC
GROUP BY stu_id ,name 

select getdate() 

select GETUTCDATE()

select year(getdate())
select month(getdate())
select day(getdate())

select datediff(YYYY,'2011-11-11','2012-12-12') --output 1 The year is indeed 1 after subtracting
select datediff(day,'2011-11-11','2012-12-12') --output 397 number of days difference between two dates

1、Get the system time SysDateTime()
2、Get the current date and time SysDateTimeOffset
3、Get the system UTC time SysUTCDateTime
4、Current_TimeStamp current database system timestamp
5, determine whether the date data isDate
		select isdate('2012-12-12') -- output 1
　　　　 select isdate('xxxx-12-12') -- output 0

SELECT MID(City,1,3) as SmallCity FROM Persons

SELECT ROUND(column_name,decimals) FROM table_name

SELECT NOW() FROM table_name

select FORMAT(stu_intime,'yyyy-MM-dd hh:mm:ss') as intime from stu

select stu_name,stu_id into stu1 from stu --insert stu_id and stu_name from the stu table into the new stu1 table (will create a new table)

/* Query m data starting from n *  declare @n int = 2;
  declare @m int = 5;
  select top (@m) * from stu
  where id not in (select top (@n) id from stu)

  /* Query data between n and m *  declare @n int = 2;
  declare @m int = 5;
  select top (@m-@n+1) * from stu
  where id not in (select top (@n-1) id from stu)

/* Query the data for ids 1 to 3 *  select * from stu where id between '1' and '3' 

 alter table stu add stu_sj varchar(200) --add a column named stu_sj

 alter table stu drop column stu_sj --delete column

/* return the name, duplicates are not returned *  select distinct stu_name from stu