1.日期函数
select current_timestamp;
select YEAR(Purchase) from buy
select convert(varchar(10),Purchase,120) from buy
select *,DATEADD(dd, 5, Purchase) from buy
SELECT ISDATE(purchase) from buy
select DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff;
select getdate();
2.字符串函数
select ASCII(buyer) from buy;
select CHARINDEX('张',buyer) from buy;
select buyer+Convert(varchar(10),Price) from buy
select *,CONCAT(number, ',', buyer, ',', Licenseplate) AS test
from buy;
select *,DATALENGTH(buyer) from buy;
select *,Len(buyer) from buy
select *,LEFT(buyer, 1) from buy
select *,LOWER(Licenseplate) from buy
select *,upper(Licenseplate) from buy
select *,LTRIM(buyer) from buy
select *,RTRIM(buyer) from buy
select *,LTRIM(RTRIM(buyer)) from buy
select *,PATINDEX('%张%',buyer) from buy
select *,REVERSE(buyer) from buy
select *,STUFF(buyer,1,3,Licenseplate) from buy
select REPLACE(buyer, '张', '李') from buy
select SUBSTRING(buyer, 1, 2) from buy
3.高级函数
select *,
case
when Price>1 then '>1'
when Price=1 then '=1'
else '1'
end
from buy;
select * from buy
order by
(case
when Price is null then number
else Price
end) desc;
select *,ISNULL(buyer, '自己设定的返回值') from buy
select *,ISNUMERIC(number) from buy