数据终于处理完了，简直神清气爽，现在想想，整体思路还是比较简单清晰的，只是过程有点艰难，常常让我觉得得多吃点坚果补补脑。以下是整理的这几天解决的问题。

1. PATINDEX函数1

查找pattern在expression中出现的位置，起始值为1，查找失败返回0。

几种形式：

-PATINDEX ( ‘%pattern%’ , expression )：

1
select patindex('%abb%','abcaabbeeabb')

结果5，也就是abb第一次出现的位置。

-PATINDEX ( ‘%pattern’ , expression )

-PATINDEX ( ‘pattern%’ , expression )

-PATINDEX ( ‘pattern’ , expression )

示例：

1
select patindex('%[^0-9]%',Line)

即取出字符串Line中第一位非数字字符的位置

2. SUBSTRING函数

1
select SUBSTRING(expression,start,length)

参数说明：

-expression：字符串、二进制字符串、文本、图像、列或包含列的表达式。请勿使用包含聚合函数的表达式。

-start：整数或可以隐式转换为 int 的表达式，指定子字符串的开始位置。

-length：整数或可以隐式转换为 int 的表达式，指定子字符串的长度。

示例:

1
select substring(Line,1,3)

从第1位开始，截取3个字符

3. 临时表2

#代表局部临时表，##代表全局临时表

-创建临时表

  1. 方法一：

create table #临时表名(字段1 约束条件,字段2 约束条件)

create table ##临时表名(字段1 约束条件,字段2 约束条件)

  1. 方法二：

select into #临时表名 from 表名
select into ##临时表名 from 表名

  • 删除临时表

drop table #临时表名;

drop table ##临时表名;

4. 向已有表中增加/删除字段

  • 添加字段：

alter table 表名 add 字段名 类型

  • 删除字段：

alter table 表名 drop column 字段名

5. 循环语句

1
2
3
4
5
6
7
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end

6. 日期时间的增加3

1
DATEADD(datepart,number,date)

参数说明：

  • date：合法的日期表达式
  • number：添加的间隔数（对于未来的时间，此数是正数，对于过去的时间，此数是负数）
  • datepart：如yy/mm/dd/hh等，对应的字母缩写见3

示例：

1
DATEADD(hh,1,time)

time增加1小时

7. datetime类型转date/转time

  • 转date:

    1
    CONVERT(varchar(10),getdate(),120)

  • 转time:

    1
    CONVERT(varchar(12),getdate(),108)

具体的数字对应的时间格式见4

8. 清空表

TRUNCATE TABLE 表名

9. 表名是变量形式

需要使用exec执行sql语句

1
2
3
4
5
declare @table varchar(100)
declare @sql varchar(100)
set @table='abc'
set @sql='select * from '+@table
exec (@sql)

10. exec里面除了表名还有其他变量

这时需要用到sp_executesql来传递参数

示例：

1
2
3
4
5
6
7
8
9
declare @count int,@tableName nvarchar(50),@SQLString nvarchar(max),@proid int,@id int,@ParmDefinition nvarchar(max);
set @tableName='table27';
set @proid=433;
set @id=159;
--set @sql=N'select @count=count(empid) from table27'
set @SQLString=N'select @countOUT=count(empid) from '+@tableName+' where proid=@proid1 and id<@id1 and state!=4';
set @ParmDefinition=N'@proid1 int,@id1 int,@countOUT   int   output';
exec sp_executesql @SQLString,@ParmDefinition,@proid1=@proid,@id1=@id,@countOUT=@count   output;
select  @count;

【注】其中@sql字符串前面的‘N’为unicode编码，不加会报错‘过程需要类型为 ‘ntext/nchar/nvarchar’ 的参数 ‘@parameters’