SQL Server关于master..spt_values的应用

wj3031

SQL Server关于master..spt_values的应用(转自CSDN)

1. select number from master..spt_values with(nolock) where type='P'
   
2. /**解释:master..spt_values表的字段值为P的对应number字段值是从0-2047*/
   
3. 
   
4. -----------
   
5. --1.将字符串转换为列显示
   
6. if object_id('tb') is not null drop table tb
   
7. go
   
8. create table tb([编号] varchar(3),[产品] varchar(2),[数量] int,[单价] int,[金额] int,[序列号] varchar(8))
   
9. insert into tb([编号],[产品],[数量],[单价],[金额],[序列号])
   
10. select '001','AA',3,5,15,'12,13,14' union all
    
11. select '002','BB',8,9,13,'22,23,24'
    
12. go
    
13. select [编号],[产品],[数量],[单价],[金额]
    
14. ,substring([序列号],b.number,charindex(',',[序列号]+',',b.number)-b.number) as [序列号]
    
15. from tb a with(nolock),master..spt_values b with(nolock)
    
16. where b.number>=1 and b.number<len(a.[序列号]) and b.type='P'
    
17. and substring(','+[序列号],number,1)=','
    
18. go
    
19. drop table tb
    
20. go
    
21. /**
    
22. 编号   产品   数量          单价          金额          序列号
    
23. ---- ---- ----------- ----------- ----------- --------
    
24. 001  AA   3           5           15          12
    
25. 001  AA   3           5           15          13
    
26. 001  AA   3           5           15          14
    
27. 002  BB   8           9           13          22
    
28. 002  BB   8           9           13          23
    
29. 002  BB   8           9           13          24
    
30. */
    
31. 
    
32. ----------
    
33. --2.第四个逗号之前的字符串
    
34. declare @str varchar(100)
    
35. set @str='10,102,10254,103265,541,2154,41,156'
    
36. ;with cte as(
    
37. select left(@str,number-1) as ss,row_number()over(order by getdate()) as xh
    
38. from master..spt_values with(nolock)
    
39. where number>=1 and number<=len(@str+',') and type='P'
    
40. and substring(@str+',',number,1)=','
    
41. )select ss from cte where xh=4
    
42. /**
    
43. ss
    
44. -------------------
    
45. 10,102,10254,103265
    
46. */
    
47. 
    
48. ----------
    
49. --3.找出两句话中相同的汉字
    
50. declare @Lctext1 varchar(100)
    
51. declare @Lctext2 varchar(100)
    
52. set @Lctext1='我们都是来自五湖四海的朋友'
    
53. set @Lctext2='朋友多了路真的好走吗'
    
54. select substring(@Lctext2,number,1) as value
    
55. from master..spt_values with(nolock)
    
56. where type='P' and number>=1 and number<=len(@Lctext2)
    
57. and charindex(substring(@Lctext2,number,1),@Lctext1,number)>1
    
58. /**
    
59. value
    
60. -----
    
61. 朋
    
62. 友
    
63. 的
    
64. */
    
65. 
    
66. ---------
    
67. --4.提取两个日期之间的所有月份
    
68. if object_id('tb') is not null drop table tb
    
69. go
    
70. create table tb(id int identity(1,1),startDate varchar(10),endDate varchar(10))
    
71. insert into tb(startDate,endDate) select '2013-01-01','2013-09-25'
    
72. go
    
73. declare @startDate varchar(10)
    
74. declare @endDate varchar(10)
    
75. select @startDate=startDate,@endDate=endDate from tb with(nolock)
    
76. select convert(varchar(7),dateadd(mm,number,@startDate),120) as [月份]
    
77. from master..spt_values with(nolock)
    
78. where type='P' and number>=0
    
79. and dateadd(mm,number,@startDate)<=@endDate
    
80. go
    
81. drop table tb
    
82. go
    
83. /**
    
84. 月份
    
85. -------
    
86. 2013-01
    
87. 2013-02
    
88. 2013-03
    
89. 2013-04
    
90. 2013-05
    
91. 2013-06
    
92. 2013-07
    
93. 2013-08
    
94. 2013-09
    
95. */
    
96. 
    
97. ---------
    
98. --5.求一个日期所在月份的所有日期
    
99. declare @date datetime
    
100. set @date='2013-08-31'
     
101. select convert(char(7),@date,120)+'-'+right('0'+convert(varchar(2),number),2) as [日期格式1]
     
102. ,ltrim(year(@date))+right(100+month(@date),2)+right('0'+ltrim(number),2) as [日期格式2]
     
103. from master..spt_values with(nolock)
     
104. where type='P' and number>=1
     
105. --and number<=datediff(dd,@date,dateadd(mm,1,@date)) --对于mssql而言该语句不试用于2013-08-31的情况，这时由于9月没有31号，固计算出来的天数是30天
     
106. and number<=datediff(dd,convert(char(7),@date,120)+'-01',convert(char(7),dateadd(mm,1,@date),120)+'-01')--转换为1号来计算天数
     
107. /**
     
108. 日期格式1       日期格式2
     
109. ----------- --------------------
     
110. 2013-08-01  20130801
     
111. 2013-08-02  20130802
     
112. 2013-08-03  20130803
     
113. 2013-08-04  20130804
     
114. 2013-08-05  20130805
     
115. 2013-08-06  20130806
     
116. 2013-08-07  20130807
     
117. 2013-08-08  20130808
     
118. 2013-08-09  20130809
     
119. 2013-08-10  20130810
     
120. 2013-08-11  20130811
     
121. 2013-08-12  20130812
     
122. 2013-08-13  20130813
     
123. 2013-08-14  20130814
     
124. 2013-08-15  20130815
     
125. 2013-08-16  20130816
     
126. 2013-08-17  20130817
     
127. 2013-08-18  20130818
     
128. 2013-08-19  20130819
     
129. 2013-08-20  20130820
     
130. 2013-08-21  20130821
     
131. 2013-08-22  20130822
     
132. 2013-08-23  20130823
     
133. 2013-08-24  20130824
     
134. 2013-08-25  20130825
     
135. 2013-08-26  20130826
     
136. 2013-08-27  20130827
     
137. 2013-08-28  20130828
     
138. 2013-08-29  20130829
     
139. 2013-08-30  20130830
     
140. 2013-08-31  20130831
     
141. */
     
142. 
     
143. ---------
     
144. --6.根据给定时间为基准以2小时为划分，得出一天划分出的时间段
     
145. declare @time varchar(5)
     
146. set @time='11:13'
     
147. select ltrim(a.number)+right(@time,3)+'-'+ltrim(b.number)+right(@time,3) as [划分结果]
     
148. from master..spt_values a with(nolock),master..spt_values b with(nolock)
     
149. where a.type='P' and b.type='P'
     
150. and a.number>=left(@time,2) and b.number<=24
     
151. and a.number+2=b.number
     
152. /**
     
153. 划分结果
     
154. -----------------------------------
     
155. 11:13-13:13
     
156. 12:13-14:13
     
157. 13:13-15:13
     
158. 14:13-16:13
     
159. 15:13-17:13
     
160. 16:13-18:13
     
161. 17:13-19:13
     
162. 18:13-20:13
     
163. 19:13-21:13
     
164. 20:13-22:13
     
165. 21:13-23:13
     
166. 22:13-24:13
     
167. */
     
168. 
     
169. ---------
     
170. --7.将字符串显示为行列
     
171. if object_id('tb') is not null drop table tb
     
172. create table tb(id int identity(1,1),s nvarchar(100))
     
173. insert into tb(s) select '车位地址1,车位状况1|车位地址2,车位状况2|车位地址n,车位状况n'
     
174. ;with cte as(
     
175. select substring(s,number,charindex('|',s+'|',number)-number) as ss
     
176. from tb with(nolock),master..spt_values with(nolock)
     
177. where type='P' and number>=1 and number<=len(s)
     
178. and substring('|'+s,number,1)='|'
     
179. )select left(ss,charindex(',',ss)-1)as s1,substring(ss,charindex(',',ss)+1,len(ss))as s2 from cte
     
180. drop table tb
     
181. /**
     
182. s1             s2
     
183. ----------- ------------
     
184. 车位地址1      车位状况1
     
185. 车位地址2      车位状况2
     
186. 车位地址n      车位状况n
     
187. */
     

复制代码