精彩SQL master..spt_values表妙用

 select number,* from master..spt_values with(nolock) where type='P'


 --取第四个逗号前字符串
 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=4 

--3.找出两句话中相同的汉字 
declare @Lctext1 varchar(100) 
declare @Lctext2 varchar(100) 
set @Lctext1='我们都是来自五湖四海的朋友'
set @Lctext2='朋友多了路真的好走吗'
select substring(@Lctext2,number,1) as value 
from master..spt_values with(nolock) 
where type='P' and number>=1 and number<=len(@Lctext2) 
and charindex(substring(@Lctext2,number,1),@Lctext1,number)>1 


--4.提取两个日期之间的所有月份 
if object_id('tb') is not null drop table tb 
go 
create table tb(id int identity(1,1),startDate varchar(10),endDate varchar(10)) 
insert into tb(startDate,endDate) select '2017-01-01','2017-09-25'
go 
declare @startDate varchar(10) 
declare @endDate varchar(10) 
select @startDate=startDate,@endDate=endDate from tb with(nolock) 
select convert(varchar(7),dateadd(mm,number,@startDate),120) as [月份] 
from master..spt_values with(nolock) 
where type='P' and number>=0 
and dateadd(mm,number,@startDate)<=@endDate 
go 
drop table tb 
go 

--6.根据给定时间为基准以2小时为划分，得出一天划分出的时间段 
declare @time varchar(5) 
set @time='11:13'
select ltrim(a.number)+right(@time,3)+'-'+ltrim(b.number)+right(@time,3) as [划分结果] 
from master..spt_values a with(nolock),master..spt_values b with(nolock) 
where a.type='P' and b.type='P'
and a.number>=left(@time,2) and b.number<=24 
and a.number+2=b.number 

--7.将字符串显示为行列 
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