日期:2014-05-17 浏览次数:20414 次
select code from tb order by code
--1)創建函數
CREATE function [dbo].[GetSplitOfIndex]
(
@String nvarchar(max), --要分割的字符串
@split nvarchar(10), --分隔符号
@index int --取第几个元素
)
returns nvarchar(1024)
--returns int
as
begin
declare @location int --标记所在位置
declare @start int --标记查找开始位置
declare @next int --
declare @seed int --标记长度
set @String=ltrim(rtrim(@String))--去掉前后空格
set @start=1
set @next=1
set @seed=len(@split) --标记长度赋值
set @location=charindex(@split,@String)
while @location<>0 and @index>@next--在检索完整个字符串或者检测到所选字符串时结束循环
begin
set @start=@location+@seed
set @location=charindex(@split,@String,@start)
set @next=@next+1
END
return
CASE
WHEN CHARINDEX(@split,@String)>0 AND @location =0 THEN ''
WHEN CHARINDEX(@split,@String)=0 AND @location =0 THEN @String
ELSE substring(@String,@start,@location-@start)
end
end
go
--2)執行查詢
;WITH a1 (cstr) AS
(
select '1' UNION ALL
select '1-1' UNION ALL
select '1-1-1' UNION ALL
select '1-2' UNION ALL
select '1-3' UNION ALL
select '1-11' UNION ALL
select '1-20' UNION ALL
select '2' UNION ALL
select '2-1' UNION ALL
select '2-1-1' UNION ALL
select '2-2' UNION ALL<