select NEWID() as GUID select GETDATE() as 日期 go --month()函数当前月时,不足10的前面补0 select '0' + convert(varchar(2),month(getdate())) --不满10补领——获取到月份 + 100 ,再获取后两位,即可获取到月份 Select Right(100 + Month(GETDATE()), 2) select convert(int,month(getdate())) --打印月份 declare @month int set @month=convert(int,month(getdate())) --------------if if @month <10 begin print( '0' + convert(varchar(2),month(getdate()))) end --------------else else begin print(convert(varchar(2),month(getdate()))) end go --返回字符串中指定的子串出现的开始位置(索引从1开始) select charindex('34','1234567890123') as startIndex --返回字符串中指定的子串出现的开始位置(索引从1开始,字串前必须加%) select patindex('%34%','1234567890123') as startIndex --大小写转化 select lower('abc') 小写,upper('abc') 大写 --去空格; LTRIM去除左侧空格; RTRIM去除左侧空格 select LTRIM(' abc ') 去除左侧空格,RTRIM(' abc ') 去除右侧空格 --截取字符串 retrun 1234 select LEFT('123456',4) as 从左侧开始截取截取4位 --return1234 select RIGHT('123456',4) as 从左侧开始截取截取4位 --return3456 --获取字符串长度 select len('helloWpf') as strLength select datalength('helloWpf') as strLength --获取数据库名称 select db_name() as 当前数据库名称 --类型转换 select 1+2 as sumno --retrun 3 select '1' + '2' as sumno --return 12 select 'a' +'b' as ab --return ab select 'abc' + convert(varchar,2) as abc2 --return asc ,注: 字符串和数字拼接时,需对数字进行类型转换,否则将报错 select convert(varchar,2.21) FloatToString select convert(varchar,2) IntToString select convert(varchar(10),getdate(),21) DateToString --(yyyy-MM-dd) 23 表示日期格式,有多种方式 --int to decimal or decimal SELECT cast('123' AS float),cast('456.233' AS decimal(18,2)) --string to datetime select cast('2012-11-11'as datetime) as StringToDatetimeByCast --datetime to varchar 无法指定日期格式 select cast(getdate() as varchar) --复制字符串2次 select replicate('I love you ',2) as 复制字符串2次 select reverse (110) as 翻转字符串 --判断表达式内容是否为数字或是否可转为数字(1--表示数字,0--表示非数字) select isnumeric(100)数字,isnumeric('100')可转为数字,isnumeric('abc')字母 --row_number 编号或说是排序函数 select row_number() over(order by student_name desc) as orderno from student --聚合函数count 、max、min、sum、avg select count(*) from student select count(1) from student -- 建议使用 select max(student_age) maxage from student select min(student_age) minage from student select sum(student_age) sumage from student select avg(student_age) avgage from student
截取小数:
------------------------截取两位小数------------------------ select cast('123.1212' AS float) select cast('456.2355' AS decimal(18,2)) select round((CONVERT(FLOAT,'456.2355')),2) select cast('123.1212' AS float)as count1,cast('456.2355' AS decimal(18,2))as count2,round((CONVERT(FLOAT,'456.2355')),2)as count3 ------------------------截取两位小数------------------------
case left:
select case when LEFT('-123456',1) ='-' then '-123456' when LEFT('-123456',1) <>'-' then '123456' end as "test"
str函数:
--STR()函数将包含4位数和小数点的数字转换为具有两个小数位的4位字符串 --参数1为原始数据 --参数2为返回值位数,例如6位数、5位数 --参数3,表示小数点后位数,例如2(位数不够则不显示) SELECT STR(123.4567, 6, 2) result SELECT STR(123.45678, 5, 2) result
示例:
set @TableNameL= select (lower(left(@TableName,1))+lower(SUBSTRING(@TableName,2,len(@TableName))) ) select (lower(left('abCdsE',1))+lower(SUBSTRING('abCdsE',2,len('abCdsE'))) ) select (lower(left('helloWpf',1))+SUBSTRING('helloWpf',2,len('helloWpf'))) ----------------------大小写转化---------------------- select upper('helloWpf') as ToUpper,lower('helloWpf') as ToLower go --示例 declare @TableNameL varchar(200), @TableName sysname = 'DictPublic' set @TableNameL = (select (lower(left('helloWpf',1))+SUBSTRING('helloWpf',2,len('helloWpf')))) set @TableName = (select (lower(left(@TableName,1))+SUBSTRING(@TableName,2,len(@TableName)))) select @TableNameL as test1,@TableName as test2 print @TableNameL print @TableName go ----------------------大小写转化---------------------- select * from VIEW_APP_RESULT where APPOINTSDATE>='2020-01-25' and APPOINTSDATE<='2023-05-30' and appkind='FSK' exec sp_helpindex his_queue go exec sp_helpindex queuedetail
博客内容主要用于日常学习记录,内容比较随意,如有问题,还需谅解!!!