sqlserver常用函数

1.字符串函数

--ascii函数，返回字符串最左侧字符的ascii码值
SELECT ASCII('dsd') AS asciistr 
--ascii代码转换函数，返回指定ascii值对应的字符
SELECT CHAR(97) AS strs
--left(c1,n)左子串函数，返回字符串c1中从左边开始的n个字符
SELECT LEFT('helloworld',4) AS lefts 
--len(c1)返回字符串c1的字符（而不是字节）数，其中不包含尾随空格
SELECT LEN('helloworld') AS lens
--lower(c1)
SELECT LOWER('HELLOWORLD') AS lowers
--LTRIM(c1)删除前导空格，返回删除了前导空格的字符串
SELECT LTRIM('  asafas') AS lt
--REPLACE(C1,C2,C3)
SELECT REPLACE('ZHESSDF-DFDSS','-','@') AS RE
--REPLICATE(C1,N)复制函数，以指定的次数n重复字符串c1
SELECT REPLICATE('这是复制函数-',3) AS rep
--right(c1,n)右子串函数，返回字符串c1中从右边开始的n个字符串
SELECT RIGHT('asdfggh',4) AS strs
--space(n) 空格函数，返回由n个重复的空格字符串
SELECT SPACE(7) AS spaces
--str(f,n,m)返回由数值数据f转换来的字符串，字符串的长度有参数n决定，字符串中保留的小数位书由M决定
SELECT STR(12.1234,9,6) AS strs
--SUBSTRING(c1,n1,n2)求子串函数，返回字符串c1从n1开始，长度为n2的子串
SELECT SUBSTRING('mylightshina',4,5)
--upper(c1)
SELECT UPPER('asdff') AS up

2.日期函数

--时间日期
SELECT  'today''s date is '+ CONVERT(VARCHAR(12), GETDATE(),102)AS da

SELECT GETDATE() 当前日期时间,
       GETUTCDATE() ut日期时间,
       DATEPART(YEAR, GETDATE()) 年,
       DATENAME(YEAR, GETDATE()) 年名,
       DATEPART(MONTH, GETDATE()) 月,
       DATENAME(MONTH, GETDATE()) 月份名,
       DATEPART(DAY, GETDATE()) 日,
       DAY(GETDATE()) 日,
       MONTH(GETDATE()) 月,
       YEAR(GETDATE()) 年,
       DATEADD(DAY, 5, GETDATE()) 新日期,
       DATEDIFF(DAY, GETDATE(), '2018-12-17') AS 日期间隔;

3.系统函数

--转换函数 cast(),convert()
SELECT CAST('2018' AS DECIMAL(10, 5)) AS deci,
       CONVERT(VARCHAR, GETDATE(), 101) AS '101',
       CONVERT(VARCHAR, GETDATE(), 102) AS '102',
       CONVERT(VARCHAR, GETDATE(), 103) AS '103',
       CONVERT(VARCHAR, GETDATE(), 104) AS '104',
       CONVERT(VARCHAR, GETDATE(), 105) AS '105',
       CONVERT(VARCHAR, GETDATE(), 106) AS '106',
       CONVERT(VARCHAR, GETDATE(), 107) AS '107',
       CONVERT(VARCHAR, GETDATE(), 108) AS '108',
       CONVERT(VARCHAR, GETDATE(), 109) AS '109',
       CONVERT(VARCHAR, GETDATE(), 100) AS '100',
       CONVERT(VARCHAR, GETDATE(), 120) AS '120',
       CONVERT(VARCHAR, GETDATE(), 121) AS '121',
       CONVERT(VARCHAR, GETDATE(), 126) AS '126',
       CONVERT(VARCHAR, GETDATE(), 130) AS '130';


--创建uniqueidentifier类型的唯一值
SELECT NEWID() AS Id
--ISNULL(c1,c2)
SELECT ISNULL(ParentId,'########'),* FROM dbo.Module
--isdate(exp)判断exp是否为有效日期
SELECT ISDATE('2018-12-11 12:2:1')
--ISNUMERIC(c1)判断c1是否是数字，是则返回1，否返回0
SELECT ISNUMERIC('1') 是,ISNUMERIC('a') 否

 4.流程控制语句

--begin...end语句块
BEGIN

    SELECT *
    FROM dbo.Role;

END;
--if...else语句
IF 1 > 4
    SELECT *
    FROM dbo.Role;
ELSE
    SELECT *
    FROM dbo.FlowInstance;
--简单case表达式
SELECT CASE ActivityType WHEN 2 THEN 'qqq' ELSE 'www' END ss ,* FROM  dbo.FlowInstance
--搜索case表达式
SELECT CASE  WHEN ActivityType>2 THEN 'qqq'ELSE 'www' END ss ,* FROM  dbo.FlowInstance

--waitfor语句
WAITFOR DELAY '00:00:03';
SELECT *
FROM dbo.Role;
--在下午八点查询输出
WAITFOR TIME '20:00:00';
SELECT *
FROM dbo.Form;
--while语句
DECLARE @sum INT,
        @i INT;
SET @i = 0;
SET @sum = 0;
WHILE @i <= 100
BEGIN
    SET @i = @i + 1;
    IF ((@i % 2) = 0)
        CONTINUE;
    SET @sum = @sum + @i;
    IF (@i > 50)
        BREAK;
END;
PRINT '1-50中的奇数和为' + CONVERT(CHAR(6), @sum);