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片断会
1

被整个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