mysql spt values_SQL Server 中master..spt_values的应用

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

/**解释:master..spt_values表的字段值为P的对应number字段值是从0-2047*/

--1.将字符串转换为列显示if object_id('tb') is not nulldrop table tb

go

create table tb([编号] varchar(3),[产品] varchar(2),[数量] int,[单价] int,[金额] int,[序列号] varchar(8))

insert into tb([编号],[产品],[数量],[单价],[金额],[序列号])select '001','AA',3,5,15,'12,13,14'union allselect '002','BB',8,9,13,'22,23,24'goselect[编号],[产品],[数量],[单价],[金额]

,substring([序列号],b.number,charindex(',',[序列号]+',',b.number)-b.number) as[序列号]fromtb a with(nolock),master..spt_values b with(nolock)where b.number>=1 and b.number

drop table tb

go/**

编号 产品 数量 单价 金额 序列号

---- ---- ----------- ----------- ----------- --------

001 AA 3 5 15 12

001 AA 3 5 15 13

001 AA 3 5 15 14

002 BB 8 9 13 22

002 BB 8 9 13 23

002 BB 8 9 13 24*/

----------

--2.第四个逗号之前的字符串

declare @str varchar(100)set @str='10,102,10254,103265,541,2154,41,156';with cteas(select left(@str,number-1) as ss,row_number()over(order by getdate()) asxhfrommaster..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

/**

ss

-------------------

10,102,10254,103265*/

----------

--3.找出两句话中相同的汉字

declare @Lctext1 varchar(100)

declare @Lctext2 varchar(100)set @Lctext1='我们都是来自五湖四海的朋友'

set @Lctext2='朋友多了路真的好走吗'

select substring(@Lctext2,number,1) asvaluefrommaster..spt_values with(nolock)where type='P' and number>=1 and number<=len(@Lctext2)

and charindex(substring(@Lctext2,number,1),@Lctext1,number)>1

/**

value

-----

朋

友

的*/

---------

--4.提取两个日期之间的所有月份if object_id('tb') is not nulldrop table tb

go

create table tb(idint identity(1,1),startDate varchar(10),endDate varchar(10))

insert into tb(startDate,endDate)select '2013-01-01','2013-09-25'go

declare @startDate varchar(10)

declare @endDate varchar(10)select @startDate=startDate,@endDate=endDate fromtb with(nolock)select convert(varchar(7),dateadd(mm,number,@startDate),120) as[月份]frommaster..spt_values with(nolock)where type='P' and number>=0and dateadd(mm,number,@startDate)<=@endDate

go

drop table tb

go/**

月份

-------

2013-01

2013-02

2013-03

2013-04

2013-05

2013-06

2013-07

2013-08

2013-09*/

---------

--5.求一个日期所在月份的所有日期

declare @date datetimeset @date='2013-08-31'

select convert(char(7),@date,120)+'-'+right('0'+convert(varchar(2),number),2) as[日期格式1]

,ltrim(year(@date))+right(100+month(@date),2)+right('0'+ltrim(number),2) as[日期格式2]frommaster..spt_values with(nolock)where type='P' and number>=1

--and number<=datediff(dd,@date,dateadd(mm,1,@date)) --对于mssql而言该语句不试用于2013-08-31的情况，这时由于9月没有31号，固计算出来的天数是30天

and number<=datediff(dd,convert(char(7),@date,120)+'-01',convert(char(7),dateadd(mm,1,@date),120)+'-01')--转换为1号来计算天数/**

日期格式1 日期格式2

----------- --------------------

2013-08-01 20130801

2013-08-02 20130802

2013-08-03 20130803

2013-08-04 20130804

2013-08-05 20130805

2013-08-06 20130806

2013-08-07 20130807

2013-08-08 20130808

2013-08-09 20130809

2013-08-10 20130810

2013-08-11 20130811

2013-08-12 20130812

2013-08-13 20130813

2013-08-14 20130814

2013-08-15 20130815

2013-08-16 20130816

2013-08-17 20130817

2013-08-18 20130818

2013-08-19 20130819

2013-08-20 20130820

2013-08-21 20130821

2013-08-22 20130822

2013-08-23 20130823

2013-08-24 20130824

2013-08-25 20130825

2013-08-26 20130826

2013-08-27 20130827

2013-08-28 20130828

2013-08-29 20130829

2013-08-30 20130830

2013-08-31 20130831*/

---------

--6.根据给定时间为基准以2小时为划分，得出一天划分出的时间段

declare @time varchar(5)set @time='11:13'

select ltrim(a.number)+right(@time,3)+'-'+ltrim(b.number)+right(@time,3) as[划分结果]frommaster..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<=24and a.number+2=b.number/**

划分结果

-----------------------------------

11:13-13:13

12:13-14:13

13:13-15:13

14:13-16:13

15:13-17:13

16:13-18:13

17:13-19:13

18:13-20:13

19:13-21:13

20:13-22:13

21:13-23:13

22:13-24:13*/

---------

--7.将字符串显示为行列if object_id('tb') is not nulldrop table tb

create table tb(idint identity(1,1),s nvarchar(100))

insert into tb(s)select '车位地址1,车位状况1|车位地址2,车位状况2|车位地址n,车位状况n';with cteas(select substring(s,number,charindex('|',s+'|',number)-number) asssfromtb 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 fromcte

drop table tb/**

s1 s2

----------- ------------

车位地址1 车位状况1

车位地址2 车位状况2

车位地址n 车位状况n*/