sql常用函数集合

-----------------sql常用函数集合-----------------
--1.ascii 返回字符表达式最左端字符的ascii码值
  SELECT ascii(1) --49
  SELECT ascii(10) --49
  SELECT ascii('A') --65
  SELECT ascii('Ab') --65
  SELECT ascii('a') --97
  SELECT ascii('aa') --97
 
--2.char() 将ascii码(0~255)转换为字符,如果超出范围,返回null只
  SELECT char(ascii('A')) --'A'
  SELECT char(ascii('Ab'))  --'A'
  SELECT char(97)-- a
  SELECT char(-1) --NULL
  SELECT char(256) --NULL
  ---打印0-255码值代表的字符-----
  declare @min INT=0,@max INT=255
  --SET @min=0
  --SET @max=255
  WHILE @min<=@max
  BEGIN
  PRINT CHAR(@min)
  SET @min=@min+1
  END
  ---打印0-255码值代表的字符-----
 
  --3.LOWER()和UPPER() 将字符窜转换成小写/大写
  SELECT lower('ASDF') --asdf
  SELECT lower('asDF') --asdf
  SELECT upper('asdf') --ASDF
 
  --4.STR(expression[,length[,decimal]]) 把数值型数据转换为字符型数据
 --参数说明:experssion 要转换的数据
 --     length 返回字符窜的长度，默认为10
 --     decimal 指定返回小数的位数 默认为0
 SELECT str(12.333333) --12   返回的小数位数为0
 SELECT str(12.333333,10)--12 返回的小数位数为0
 SELECT str(12.333333,10,6) --12.333333 返回6位小数
 SELECT str(12.333333,10,5) --12.33333  返回5位小数
 
 --5.LTRIM()/RTRIM() 把字符串头部/尾部的空格去掉。
   SELECT ltrim(' LTRIM')--LTRIM
   SELECT ltrim(' LTRIM ')--LTRIM 注意：尾部的空格没有去掉
   SELECT rtrim('RTTIM ') --RTTIM
   SELECT rtrim(' RTTIM ') -- RTTIM 注意：头部的空格没有去掉
   --同时去掉头部和尾部的空格
   SELECT rtrim(ltrim(' LTRIMRTRIIM '))--LTRIMRTRIIM
  
 --6.LEFT(char_expression,int_experssion) 返回字符窜char_expression从左边开始int_experssion个字符
 --            从1开始，返回int_experssion字符
   SELECT left('hello,world',0) -- 空值
   SELECT LEFT('hello,world',1) --h
   SELECT LEFT('hello,world',2)--he
   SELECT LEFT('hello,world',15) --超出长度，返回整个字符窜 hello,world
  
 --7.RIGHT(char_expression,int_experssion) 返回字符窜char_expression从右边开始int_experssion个字符
 --            从1开始，返回int_experssion字符
   SELECT right('hello,world',0) -- 空值
   SELECT right('hello,world',1) --d
   SELECT right('hello,world',2)--ld
   SELECT right('hello,world',15) --超出长度，返回整个字符窜 hello,world
  
--8.SUBSTRING(expression,int_startposition,int_length) 返回字符窜左边第int_startposition个字符起int_length个字符
--                从1开始
  SELECT substring('hello,world',0,1) --空值
  SELECT substring('hello,world',1,1)--h
  SELECT substring('hello,world',1,15) --hello,world
  --SELECT substring(123456,1,1) --报错
  --截取全部字符窜
  SELECT substring('hello,world',1,len('hello,world'))--hello,world 说明：len(expressoin)返回expression的长度
  SELECT len(123) --3
  SELECT len('hello,world')--11
 
--9.CHARINDEX(subexpression,expression[,int_startposition]) 返回subexpression在expression在第int_startposition字符起(包括这个字符)第一次出现的位置,如果找不到，返回0
--               不区分大小写,不能用于TEXT 和IMAGE 数据类型。
  SELECT charindex('h','hello,world')--1
  SELECT charindex('o','hello,world',5)--5 从hello,world字符窜第5个字符起o在hello,world出现的位置
  SELECT charindex('o','hello,world',6) --8
  SELECT charindex('o','hello,world',15) --0 找不到，返回0
  SELECT charindex('o,','HELLO,world') --5 
 
--10.PATINDEX(%subexpression%,expression) 返回subexpression在expression出现的位置，subexpression必须有%%包括，否则返回0
--            与CHARINDEX 函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。
  SELECT patindex('hello','hello,world') --0 没有使用%%
  SELECT patindex('%hello%','hello,world')--1
  SELECT patindex('%_ll_%','hello,world')--2 使用通配符 相当于ello出现的位置
  SELECT patindex('%__ll_%','hello,world')--1 使用通配符 相当于hello出现的位置
  SELECT patindex('%___ll_%','hello,world')--0 使用通配符 相当于*hello出现的位置
 
--11.QUOTE(expression[,quote_character]) 返回被特定字符括起来的字符窜，默认为[]
  SELECT quotename('BILLIAM') --[BILLIAM]
  SELECT quotename('BILLIAM','(')--(BILLIAM)
  SELECT quotename('BILLIAM','{')--{BILLIAM}
  SELECT quotename('BILLIAM','a')--NULL
 
--12.REPLICATE(string_expression,int)返回一个重复character_expression 指定次数的字符串。
  SELECT replicate('billiam',1) --billiam 重复billiam 1次
  SELECT replicate('billiam',2) --billiambilliam 重复billiam 2 次
 
--13.REVERSE(char_expression)将指定的字符串的字符排列顺序颠倒。
  SELECT reverse('BILLIAM') --MAILLIB
  SELECT reverse(123) --321
 
--14.REPLACE(exp1,exp2,exp3)用exp3代替exp2在exp1出现的位置
  SELECT replace('hello,world!hello,china','hello','hi')--hi,world,hi,china
 
--15.SPACE(int)返回一个有指定长度的空白字符串。
  SELECT space(-1) --NULL
  SELECT space(0)-- 0个空白
  SELECT space(1)-- 1个空白
 
--16.STUFF(character_expression1,start_pos,int_length,character_expression2)用子串character_expression2替换字符串character_expression1指定位置、长度的子串。
  SELECT stuff('hello,world',1,5,'hi')--hi,world
 
--17.CAST(expression as data_type[length])--转换数据类型 data_type为sql系统定义的类型，length长度默认为30
  SELECT cast('2012-6-5' AS DATE) --2012-06-05
  SELECT cast('2012-6-5' AS DATETIME) --2012-06-05 00:00:00.000
  SELECT cast('2012-6-5' AS DATETIME2) --2012-06-05 00:00:00.0000000
  SELECT cast('2012-6-5' AS DATETIMEOFFSET) --2012-06-05 00:00:00.0000000 +00:00
  SELECT cast('2012-6-5' AS SMALLDATETIME) --2012-06-05 00:00:00
  SELECT CAST(dateadd(hour,14,getdate()) AS CHAR) --06 10 2012  2:44AM   06 10 2012  4:46PM            以AM PM标识上午还是下午 
  SELECT CAST(dateadd(hour,14,getdate()) AS CHAR(10)) --06 10 2012
  SELECT cast(getdate() AS VARCHAR)    --06 10 2012  2:48AM  
  SELECT cast('1' AS INT) --1
  SELECT cast('-1' AS INT) -- -1
  --SELECT cast('A' AS INT) --转换失败
--18.CONVERT(data_type[length],expression[,style])
 --1）data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。
 --2）length用于指定数据的长度，缺省值为30。
 --3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
 --4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。
 --5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。
 --6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。
 --7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。
 --8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。
 --9）用CONVERT（）函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。
   --具体样式的地址 http://msdn.microsoft.com/zh-cn/library/ms187928.aspx
   SELECT convert(CHAR,getdate(),100)--06 10 2012 12:09PM           
   SELECT convert(CHAR,getdate(),101)--06/10/2012                    长度为30
   SELECT convert(CHAR,getdate(),102)--2012.06.10                    长度为30
   SELECT convert(CHAR,getdate(),103)--10/06/2012                    长度为30
   SELECT convert(CHAR,getdate(),104)--10.06.2012                    长度为30
   SELECT convert(CHAR,getdate(),105)--10-06-2012                    长度为30
   SELECT convert(CHAR,getdate(),106)--10 06 2012                     长度为30
   SELECT convert(CHAR,getdate(),107) --06 10, 2012                   长度为30
   --SELECT convert(DATETIMEOFFSET(7),getdate())
--19.DAY(date_expression) 返回date_expression的日期值
 SELECT day('2012-6-6')--6
 SELECT day(getdate()) --10
 SELECT day(dateadd(day,-6,getdate())) --4
 
--20.MONTH(date_expression) 返回date_expression的月份值
 SELECT month('2012-6-6')--6
 
--21.YEAR(date_expression) 返回date_expression的年份值
 SELECT year('2012-6-6')--2012
 
--22.DATEADD(depart,number,date) 返回与date相隔number个depart的日期(datetime)
--depart的取值范围 (year,quarter,month,dayofyear,day,week,weekday,hour,minute,second,millisecond)
                       --getdate()的日期为2012-6-10
 SELECT dateadd(year,1,getdate()) --2013-06-10 18:14:12.610
 SELECT dateadd(quarter,1,getdate()) --2012-09-10 18:14:47.053 日期的月份加上3个月
 SELECT dateadd(month,1,'2012-8-31') --2012-09-30 00:00:00.000 9月份没有31号
 SELECT dateadd(dayofyear,1,getdate()) --2012-06-11 18:17:20.220
 SELECT dateadd(week,1,getdate()) --2012-06-17 18:21:25.440
 SELECT dateadd(weekday,10,getdate()) --2012-06-20 18:23:26.803
 
--23.DATEDIFF (<datepart>， <date1>， <date2>) 返回两个指定日期在datepart 方面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。
 SELECT datediff(year,'2011-3-4',getdate())  --1 
 SELECT datediff(year,'2013-3-4',getdate())  -- -1 
 SELECT datediff(dayofyear,'2012-3-4',getdate())  -- 98
 SELECT datediff(dayofyear,'2012-5-30',getdate())  -- 11
 SELECT datediff(day,'2012-5-30',getdate())  --11
 SELECT datediff(week,'2012-5-30','2012-6-10')  --2  select datename(week,getdate()) 返回24 select datename(week,'2012-5-30') 返回22
 SELECT datediff(weekday,'2012-5-30','2012-6-10') --11
 
--24.DATENAME (<datepart>， <date>) 以字符串的形式返回日期的指定部分此部分。由datepart 来指定。
         --depart的取值范围 (year,quarter,month,dayofyear,day,week,weekday,hour,minute,second,millisecond)
         --各种depart得出的范围:
         --year 0-9999
         --quarter 1-4
         --month 1-12
         --dayofyear 1-365
         --day 1-31
         --week 1-53
         --weekday 星期一 .....................星期日
  SELECT datename(year,getdate()) --2012
  SELECT datename(quarter,getdate()) --2 6月份为2012的第二季
  SELECT datename(month,getdate()) --06
  SELECT datename(dayofyear,getdate()) --162 今年的第162天
  SELECT datename(week,getdate())--24 getdate为2012-6-10 返回为今年的第24周
  SELECT datename(day,getdate()) --10 6月份的10号
  SELECT datename(WEEKDAY,getdate()) --星期日
  SELECT datename(HOUR,getdate()) --18
 
--25.DATEPART (<datepart>， <date>)以整数值的形式返回日期的指定部分。此部分由datepart 来指定。
         --DATEPART (DAY， date) 等同于DAY (date)
         --DATEPART (MONTH， date) 等同于MONTH (date)
         --DATEPART (YEAR， date) 等同于YEAR (date)
         
 SELECT datepart(day,getdate()) --10
 SELECT datepart(DAYOFYEAR,getdate()) --162
 SELECT datepart(week,getdate()) --24
 SELECT datepart(WEEKDAY,getdate()) --1 星期日 一个星期的第一天
 SELECT datepart(quarter,getdate()) --2
 
--26.GETDATE()以DATETIME 的缺省格式返回系统当前的日期和时间。
 SELECT getdate()--2012-06-10 18:57:38.077
 
--27.内部合计函数
    --1）COUNT（*） 返回行数
    --2）COUNT（DISTINCT COLNAME）返回指定列中唯一值的个数
    --3）SUM（COLNAME/EXPRESSION）返回指定列或表达式的数值和；
    --4）SUM（DISTINCT COLNAME） 返回指定列中唯一值的和
    --5）AVG（COLNAME/EXPRESSION）返回指定列或表达式中的数值平均值
    --6）AVG（DISTINCT COLNAME） 返回指定列中唯一值的平均值
    --7）MIN（COLNAME/EXPRESSION）返回指定列或表达式中的数值最小值
    --8）MAX（COLNAME/EXPRESSION）返回指定列或表达式中的数值最大值
 SELECT count(*) FROM Employee e --8
 SELECT count(DISTINCT DEPART) FROM Employee e --2
 SELECT sum(e.Salary) FROM Employee e --36
 SELECT sum(DISTINCT e.Salary) FROM Employee e --36

-----------------sql常用函数集合-----------------