Sql Server 实用函数方法汇总_sql server pos 函数-优快云博客

实用函数方法

由于有些知识很少被用到，但真需要用时却忘记了又焦头烂额的到处找。

现在将这些‘冷门“却有效的小知识贡献出来，以备不时之需。

存储过程中的
''''

相当于数据库中的‘ 单引号

DECLARE

@str VARCHAR(100)

SET

@str='''aaa'''

SELECT

REPLACE(@str,'''','"')

："aaa"

rtrim
 :使用 LTRIM 删除字符变量中的前导空格 ; RTRIM 删除字符变量中的尾随空格

rtrim(ltrim(splitdata))

--
 用select into 把数据放到临时表中，按交费期限排序，并加上id

select

identity(int,1,1)
as

id,FeeGUID,PayLimit,BgnDate,EndDate,Amount,TestIsRight 

into

#tmpfee --select
 into不需要提前声明临时表#tmpfee

from

z_fee 

where

RentGUID = @strRentGUID

order

by 
PayLimit

drop

table 
#tmpfee

用insert

into 
select 
创建临时表 插入自增列

  Create

Table 
#Temp_ProjectCodeList

        (

            RowId
int

identity(1,1)

            ,ProjectCode
varchar(100)

        )

        Insert

Into 
#Temp_ProjectCodeList(ProjectCode)

        Select

ProjCode From

p_Project Where

Level 
= 2

        Set

@MaxCount = @@RowCount

        Set

@Count

= 1

        While
 @Count 

@MaxCount

        Begin

            Select

@ProjectCode = ProjectCode From

#Temp_ProjectCodeList Where

RowId = @Count

            Exec

usp_cb_BuildHsCost @ProjectCode, @IsExistHsCost, @IsUpdateCsCost

            --调整计数器

            Set

@Count

= @Count

+ 1

        End

Return

执行不成功，中断执行

If
 Exists(select

8 from

cb_HsCost where

ProjectCode = @ProjectCode and

IsJianAn = 1)

   If
 @ProjectGUID Is

Null

    Begin

        Print
'['

+ @ProjectCode + ']：当前指定的项目在当前系统中不存在！'

        Return

-1

    End

得到包含前月在内的一年时间

SET

@dtBeginDate = getdate()

SET

@dtEndDate = dateadd(month,-1,dateadd(year,1,@dtBeginDate))

高能预警

DATEPART
 ( datepart , date

) datepart函数中一周是周日到周六，而我们通常认为一周是周一到周日

 返回表示指定日期的指定日期部分的整数

本周第一天
 (星期1)

select

dateadd(wk, datediff(wk,0,getdate()), 0)  

本周最后一天（星期天）

select

dateadd(wk, datediff(wk,0,getdate()), 6) 

得到上周一的日期：

 SELECT

DATEADD(day,-DATEPART(weekday,getdate())-5,getdate())

得到上周日的日期：

SELECT

DATEADD(day,-DATEPART(weekday,getdate())+1,getdate())

得到上个月月末日期：

SELECT

dateadd(day,-datepart(day,getdate()),getdate())

上月第一天

SELECT

CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),111) 

本月第一天

select

dateadd(dd,-datepart(dd,getdate())+1,getdate())  

本月最后一天(当前为2011-03-31时会出错)
 选用：select

dateadd(dd,-DAY(dateadd(mm,1,'2011-12-20'))
 ,dateadd(mm,1,'2011-12-20'))

select

dateadd(dd,-datepart(dd,getdate()) ,dateadd(mm,1,getdate()))  

下月第一天 

select

dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate()))  

下月最后一天

SELECT

CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+2,0)),111)+'
 23:59:59' 

季度第一天

SELECT  

DATEADD(qq,   DATEDIFF(qq,0,getdate()),   0)   

季度最后一天（直接推算法） 

SELECT

DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,getdate())*3-Month(getdate()),getdate()),120)+'1') 

季度的最后一天（CASE判断法） 

select

DATEADD(Month,DATEPART(Quarter,getdate())*3-Month(getdate()),getdate()) 

本月第一个星期一

SELECT

DATEADD(wk, DATEDIFF(wk, '',
 DATEADD(dd, 6 - DAY(getdate()),
 getdate())), '')  

今年第一天 

SELECT  

DATEADD(yy,   DATEDIFF(yy,0,getdate()),   0)   

今年最后一天 

SELECT 

dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0))  

指定日期所在周的任意一天 

SELECT

DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)--5．指定日期所在周的任意星期几 

A. 
 星期天做为一周的第1天  

SELECT

DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt) 

B. 
 星期一做为一周的第1天  

SELECT

DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt) 

周内的第几日 

select

datepart(weekday,getdate()) as

周内的第几日  

年内的第几周 

select

datepart(week,getdate()) as

年内的第几周  

年内的第几季 

select

datepart(quarter,getdate()) as

年内的第几季

快速高效创建数字辅助表

--创建数字辅助表

SET

NOCOUNT ON

 IF
 OBJECT_ID('dbo.Nums')
IS

NOT 
NULL 
DROP 
TABLE 
dbo.Nums;

 CREATE

TABLE 
dbo.Nums(n INT

NOT 
NULL 
PRIMARY 
KEY 
);

 DECLARE

<a href="http://www.jobbole.com/members/wx2197377149">@max</a>
AS

INT 
,@rc AS

INT 
;

 SET

@max=10000;

 SET

@rc=1;

 INSERT

INTO 
dbo.Nums VALUES

(1);

 WHILE
 @rc * 2  <a href="http://www.jobbole.com/members/wx2197377149">@max</a>

 BEGIN

 INSERT

INTO 
dbo.Nums SELECT

n + @rc FROM

dbo.Nums ;

 SET

@rc = @rc * 2;

 END

 INSERT

INTO 
dbo.Nums 

 SELECT

n +  @rc FROM

dbo.Nums WHERE

n + @rc  @max;

SELECT

COUNT 
(n) FROM

Nums

练习：将下面表1每行字符串转化为表2格式

/*PlanDetailID 
 Description

1   
 课程详细安排1,课程详细安排1.1,课程详细安排1.2,课程详细安排1.3

2   
 课程详细安排2,课程详细安排2.1,课程详细安排2.2

3   
 课程详细安排3,课程详细安排3.1,课程详细安排3.2,课程详细安排3.3,课程详细安排3.4

4   
 课程详细安排4

5   
 课程详细安排5

 

转化为：

PlanDetailID
 pos Description

1   
 1    课程详细安排1

1   
 2    课程详细安排1.1

1   
 3    课程详细安排1.2

1   
 4    课程详细安排1.3

2   
 1    课程详细安排2

2   
 2    课程详细安排2.1

2   
 3    课程详细安排2.2

3   
 1    课程详细安排3

3   
 2    课程详细安排3.1

3   
 3    课程详细安排3.2

3   
 4    课程详细安排3.3

3   
 5    课程详细安排3.4

4   
 1    课程详细安排4

5   
 1    课程详细安排5

*/

参考SQL：

--生成副本，按逗号的个数，n为逗号的位置（默认第一位为逗号）

SELECT 

PlanDetailID ,

        Description
 ,

        n

FROM   

dbo.T_PlanDetail

        INNER

JOIN 
dbo.Nums ON

n  LEN(Description) + 1   --若无AND，则表示按字符个数来生成行数

                               AND

SUBSTRING(','

+ Description, n, 1) = ','

;--将含有逗号时候的行输出

--计算每一个字符串的长度

SELECT 

PlanDetailID ,

        SUBSTRING(Description,
 n, CHARINDEX(',',
 Description + ',',
 n) - n) AS

element--元素的长度等于下一个逗号的位置减该元素的开始位置

FROM   

dbo.T_PlanDetail

        INNER

JOIN 
dbo.Nums ON

n  LEN(Description) + 1   --若无AND，则表示按字符个数来生成行数

                               AND

SUBSTRING(','

+ Description, n, 1) = ','

;--将含有逗号时候的行输出

--计算每个字符串在数组中的位置,按PlanDetailID
 分区，按 n 排序

SELECT 

PlanDetailID ,ROW_NUMBER() OVER(PARTITION BY

PlanDetailID ORDER

BY 
n) AS

pos,

        SUBSTRING(Description,
 n, CHARINDEX(',',
 Description + ',',
 n) - n) AS

element

FROM   

dbo.T_PlanDetail

        INNER

JOIN 
dbo.Nums ON

n  LEN(Description) + 1   --若无AND，则表示按字符个数来生成行数

                               AND

SUBSTRING(','

+ Description, n, 1) = ','

;--将含有逗号时候的行输出

在sql
 server中经常有这样的问题：

一个表采用了自动编号的列之后，由于测试了好多数据，自动编号已累计了上万个。

现在正是要用这个表了，测试数据已经删了，遗留下来的问题
 就是在录入新的数据，编号只会继续增加，已使用过的但已删除的编号就不能用了，

 谁知道如何解决此问题？

truncate命令不但会清除所有的数据，还会将IDENTITY的SEED的值恢复到原是值。