http://blog.sina.com.cn/liuyibo1983

master..spt_values可以理解为数据库常量表，spt_values表是在master数据库下，故书写为master..spt_values（master..spt_values相当于master.dbo.spt_values）。

select * from master..spt_values

-- 5个字段分别表示为 名称、值、类型、下限、上限、状态

1.number（TYPE='P'）的范围在0-204 7

SELECT number FROM master..spt_values WHERE  TYPE = 'p'  

2.取当前日期到以后的2047个日期

SELECT CONVERT(CHAR(10), DATEADD(DAY, number, GETDATE()), 120) AS [日期]

FROM MASTER..spt_values WHERE TYPE='P' AND number>0

3.取1-1000之间的数字

select number from master..spt_values where type='P' and number between 1 and 1000

4.取2018年1月至2018年5月的月份

create table Mon

(

ID int identity(1,1),

Mon varchar(6)

)

GO

DECLARE

@BeginMonth varchar(6),

@EndMonth varchar(6)

SELECT

@BeginMonth='201801',

@EndMonth='201805'

INSERT Mon(Mon)

SELECT

CONVERT(VARCHAR(6),DATEADD(month,number,@BeginMonth+'01'),112)

FROM

master..spt_values

WHERE

type='P'

and

DATEADD(month,number,@BeginMonth+'01')<=@EndMonth+'01'

SELECT * FROM Mon

DROP TABLE Mon

5.取两句中重复的汉字

declare @Lctext1 varchar(100),@Lctext2 varchar(100)

set @Lctext1='十年前我们在一起'

set @Lctext2='十年后我们又重聚在一起'

SELECT SUBSTRING(@Lctext2,number,1) as value

from master..spt_values

where type='p' and number <=LEN(@Lctext2)and CHARINDEX(SUBSTRING(@Lctext2,number,1),@Lctext1)>0

6.MASTER.dbo.spt_values 扩展应用——按半小时分组

DECLARE @t TABLE (

d DATETIME

)

INSERT INTO @t (d) VALUES( '2016-08-21 18:13' )

INSERT INTO @t (d) VALUES( '2016-08-21 18:51' )

;WITH t AS (

SELECT number,CONVERT(CHAR(8), DATEADD(n, (number-1)*30, CONVERT(CHAR(10), GETDATE(),120)),108) AS beginTimeSeg

, CONVERT(CHAR(8), DATEADD(n, (number)*30, CONVERT(CHAR(10), GETDATE(),120)),108) AS endTimeSeg

FROM MASTER.dbo.spt_values AS SV WHERE SV.type='P' AND SV.number BETWEEN 1 AND 48

)

,t2 AS (

SELECT *, (SELECT t.number FROM t WHERE CONVERT(CHAR(8),a.d,108) >=t.beginTimeSeg AND CONVERT(CHAR(8),a.d,108)

)

SELECT a.d,a.seg,b.beginTimeSeg,b.endTimeSeg FROM t2 A INNER JOIN t B ON a.seg=b.number

7.获取这两个日期之间的日期序列

--@begin与@end可以认为是多外面传来的两个参数，

--用了master.dbo.spt_values表其中的type与number两列

declare @begin datetime,@end datetime

set @begin='2012-1-1'

set @end='2012-1-5'

declare @days int

set @days=DATEDIFF(dd,@begin,@end)

select DATEADD(dd,number,@begin)

from master.dbo.spt_values

8.取第四个逗号之前的字符串

declare @str varchar(100)

set @str='10,102,10254,103265,541,2154,41,156'

;with cte as(

select left(@str,number-1) as ss,row_number()over(order by getdate()) as xh

from master..spt_values with(nolock)

where number>=1 and number<=len(@str+',') and type='P'

and substring(@str+',',number,1)=','

)select ss from cte where xh=3

9.将字符串显示为行列

if object_id('tb') is not null drop table tb

create table tb(id int identity(1,1),s nvarchar(100))

insert into tb(s) select '车位地址1,车位状况1|车位地址2,车位状况2|车位地址n,车位状况n'

;with cte as(

select substring(s,number,charindex('|',s+'|',number)-number) as ss

from tb with(nolock),master..spt_values with(nolock)

where type='P' and number>=1 and number<=len(s)

and substring('|'+s,number,1)='|'

)select left(ss,charindex(',',ss)-1)as s1,substring(ss,charindex(',',ss)+1,len(ss))as s2 from cte

drop table tb