ms sql 常用基础函数

sql server:
1、在插入语句时，返回主键或者其他
insert into tabname(name) output inserted.id valus(‘test’)
2、游标的使用
3、数字的字符串转为数字
select cast(‘01’ as int)
4、在一个字符串中传入一个str
stuff(‘test’,2,2,‘test’)
结果：‘ttestt’
ps:第一个2：第一个str的开始位置
第二个2：第一个str的长度
5、字符串的长度len(‘str’)

6、字符串的截取
select SUBSTRING(‘test’,2,3)
第二个参数：str的开始位置（sql从一开始）
第三个参数：str的长度
7、字符串的替换
select replace(‘test’,‘t’,‘test’)
第一个参数：要操作的对象
第二个参数：就是要操作的所含有的字符
第三个参数：替换的对象

8、
select * from syscolumns
ps:
syscolumns表中包含当前数据库的所有表的字段名称(syscolumns.name中)
select * from sysobjects
ps:sysobjects表中包含当前数据库的表名
比如:select name from sysobjects where xtype=‘U’
就是查找当前数据库中所有表名，就是当xtype='U’时，就是当前数据库中表名

问题一：查找当前数据库中所有字段中包含有test的字段的表名和字段名
方法一：
select from syscolumns a
left outer join sysobjects b
on a.id=b.id
where b.xtype=‘u’ and a.name like ‘%test%’
方法二：
SELECT TABLE_NAME,column_name FROM INFORMATION_SCHEMA.Columns where COLUMN_NAME like ‘%test%’
表名为 information_schema.columns

9、
sql server函数replicate
select replicate('',4)
第一个参数为操作对象，
第二参数为整数，对第一个参数进行叠加如下：***
结果：对第一个参数使用第二参数的倍增。
10、
CHARINDEX函数
CHARINDEX函数返回字符或者字符串在另一个字符串中的起始位置。CHARINDEX函数调用方法如下：
CHARINDEX ( expression1 , expression2 [ , start_location ] )
Expression1是要到expression2中寻找的字符中，
start_location是CHARINDEX函数开始在expression2中找expression1的位置。
如：
CHARINDEX(’SQL’, ’Microsoft SQL Server’)
这个函数命令将返回在“Microsoft SQL Server”中“SQL”的起始位置，
在这个例子中，CHARINDEX函数将返回“S”在“Microsoft SQL Server”中的位置11。
接下来，我们看这个CHARINDEX命令：
CHARINDEX(’7.0’, ’Microsoft SQL Server 2000’)
11、时间相差函数：
select DATEDIFF(year,‘2012’,‘2013’)
结果：1
select DATEDIFF(month,‘2012’,‘2013’)
结果为：12
select DATEDIFF(day,‘2012’,‘2013’)
结果为：366
ps：结果是第三个参数减去第二个参数
12、时间相加函数：
select dateadd(day,2,‘2013-1-2’)
结果：2013-1-4 后面是时分秒
select dateadd(month,2,‘2013-1-2’)
结果：2013-3-2 后面是时分秒
select dateadd(year,2,‘2013-1-2’)
结果：2015-1-2 后面是时分秒
注意：第二参数必须为int型
让第二个参数对第三个参数进行对应的操作，如果day,month,year等操作
13、获取对应日期的值
select day(‘2013-01-23’) day值
select month(‘2013-01-03’) month值
select year(‘2013-01-03’)year值
14、长度函数
select datalength(getdate())时间对应的长度
结果：8
select len(getdate())将时间转为字符串的长度
结果：18
15、对字符串的截取
select right(‘12233fafdsa’,2)
结果：sa
select left(‘afasdf12233fafdsa’,2)
结果：af
第一个参数为操作的对象
第二个参数为在第一个参数在对应的left,right截取对应的长度

16、with as的用法
ps：WITH AS的含义

WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会

被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数
据的部分。
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，
所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将
WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS
短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

with
cr as
(
select name from tab_test where Name like ‘t%’
)

select * from tab_test where name in (select * from cr)
select * from tab_test where name like ‘t%’

with
cte1 as
(
select * from tab_test where name like ‘t%’
),
cte2 as
(
select * from tab_test where id > 20
),
cte3 as
(
select * from tab_test where price > 100
)
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id

17.将字符串反序：
select reverse(‘12334’)
结果：43321