精彩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