现有一条数据:7856321-5,7936783
如何输出7856321 7856322 7856323 7856324 7856325 7936783这六条语句
具体规律就是数据需要‘,’划分,若有‘-’,则‘-’也需要划分,如7856321-5需要划分为7856321到7856325五条数据,那位大牛指教一下啊,谢谢诶
如何输出7856321 7856322 7856323 7856324 7856325 7936783这六条语句
具体规律就是数据需要‘,’划分,若有‘-’,则‘-’也需要划分,如7856321-5需要划分为7856321到7856325五条数据,那位大牛指教一下啊,谢谢诶
declare @b nvarchar(10)set @a='7856321-5'
set @b='7936783'
declare @Count int
set @count=convert(int,right(@a,len(@a)-charindex('-',@a)))
select @countdeclare @Out int
set @Out=convert(int,left(@a,charindex('-',@a)-1))declare @Print nvarchar(100)
set @Print=''
while @count>0
begin
set @Print=@Print + convert(nvarchar(10),@Out)+ ','
set @Out=@Out+1
set @Count=@Count-1
end
set @Print=@Print+ @bselect @Print代码有些粗糙
SET @str = '7856321-5,7936783';WITH Liang AS
(
SELECT B.x.value('.','VARCHAR(MAX)') AS v
FROM (
SELECT CONVERT(XML,'<v>'
+REPLACE(@str,',','</v><v>')+'</v>') AS v
) AS T
CROSS APPLY T.v.nodes('//v') AS B(x)
)
SELECT
lv + number AS number
FROM (
SELECT CASE WHEN CHARINDEX('-',v)>0
THEN CAST(LEFT(v,CHARINDEX('-',v)-1) AS INT)
ELSE v END AS lv,
CASE WHEN CHARINDEX('-',v)>0
THEN CAST(RIGHT(v,CHARINDEX('-',REVERSE(v))-1) AS INT)-1
ELSE 0 END AS rv
FROM Liang
) AS T
JOIN master.dbo.spt_values AS B
ON B.type = 'p' AND B.number >=0 AND B.number <= T.rv
ORDER BY 1/*
number
-----------
7856321
7856322
7856323
7856324
7856325
7936783(6 row(s) affected)*/
SELECT @STR='7856321-5,7936783',@STR2='',@I=SUBSTRING(@STR,CHARINDEX('-',@STR)+1,CHARINDEX(',',@STR)-CHARINDEX('-',@STR)-1)
,@J=SUBSTRING(@STR,CHARINDEX('-',@STR)+1,CHARINDEX(',',@STR)-CHARINDEX('-',@STR)-1)
WHILE @I>0
SELECT @STR2=@STR2+' '+CONVERT(VARCHAR(100),CONVERT(BIGINT,LEFT(@STR,CHARINDEX('-',@STR)-1))+@J-@I),@I=@I-1
SELECT @STR2+' '+SUBSTRING(@STR,CHARINDEX(',',@STR)+1,LEN(@STR)-CHARINDEX(',',@STR))
-- 7856321 7856322 7856323 7856324 7856325 7936783
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-31 14:18:32
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([col] nvarchar(17))
Insert tb
Select '7856321-5,7936783'
Go
--Select * from tb-->SQL查询如下:
;with t as
(
select
rn=row_number() over(order by getdate()),
COL=substring(a.COL, b.number, charindex(',', a.COL + ',', b.number) - b.number)
from tb a,master..spt_values b
where b.type='p'
and substring(',' + a.COL,b.number,1) = ','
),t1 as
(
select rn,
case when charindex('-',col)>0 then left(col,charindex('-',col)-1) else col end as col,
case when charindex('-',col)>0 then right(col,charindex('-',reverse(col))-1) else 1 end as num
from t
)
select col+number from t1,master..spt_values where type='p' and number<num
/*
-----------
7856321
7856322
7856323
7856324
7856325
7936783(6 行受影响)
*/
declare @s varchar(80),@i int,@str varchar(400)
set @str=''
set @s='7856321-5,7936783'
select @i=substring(@s,charindex('-',@s)-1,1)
while @i<=substring(@s,charindex('-',@s)+1,1)
begin
set @str=@str+' '+left(@s,charindex('-',@s)-2)+cast(@i as varchar(10))
set @i=@i+1
end
set @str=@str+' '+right(@s,len(@s)-charindex(',',@s))
select @str/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7856321 7856322 7856323 7856324 7856325 7936783(所影响的行数为 1 行)
*/
set @s='7856321-5,7936783'
while CHARINDEX('-',@s)>0
begin
declare @n int
set @n=SUBSTRING(@s,CHARINDEX('-',@s)+1,1)
set @s=STUFF(@s,CHARINDEX('-',@s),3,' ')
end
select LEFT(@s,charindex(' ',@s)-1)+1
declare @str varchar(100),@num int
set @num=1
while @num<=@n
begin
set @str=isnull(@str+' ','')+rtrim((LEFT(@s,charindex(' ',@s)-1)+@num-1))
set @num=@num+1
end
select @str + +' '+RIGHT(@s,len(@s)-CHARINDEX(' ',@s))
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7856321 7856322 7856323 7856324 7856325 7936783
create function k (@s varchar(100))
returns varchar(100)
as
begin
while CHARINDEX('-',@s)>0
begin
declare @n int
set @n=SUBSTRING(@s,CHARINDEX('-',@s)+1,1)
set @s=STUFF(@s,CHARINDEX('-',@s),3,' ')
end
select LEFT(@s,charindex(' ',@s)-1)+1
declare @str varchar(100),@num int
set @num=1
while @num<=@n
begin
set @str=isnull(@str+' ','')+rtrim((LEFT(@s,charindex(' ',@s)-1)+@num-1))
set @num=@num+1
end
return @str+' '+RIGHT(@s,len(@s)-CHARINDEX(' ',@s))
end
select col1,dbo.k(col1) from tb
set @s='7856321-5,7936783' declare @res varchar(2000)
declare @i int
set @i=0
while @i<substring(@s,charindex('-',@s)+1,1)
begin
set @res=isnull(@res+',','')+ ltrim(substring(@s,1,charindex('-',@s)-1)+@i)
set @i=@i+1
end
set @res=@res+','+substring(@s,charindex(',',@s)+1,len(@s)-charindex(',',@s))
print @res/*
7856321,7856322,7856323,7856324,7856325,7936783
*/
DECLARE @memo VARCHAR(1000)
DECLARE @temp VARCHAR(1000)
DECLARE @tmp VARCHAR(1000)
declare @len int
declare @min int
declare @max intSET @string='7856321-0,7936783-9,7936799'
set @memo=''
set @len = charindex(',',@string)
--select @len
while (@len > 0 )
begin
set @temp=''
set @temp=substring(@string,1,@len)
set @temp = left(@string,@len-1)
if charindex('-',@temp) > 0
begin
set @tmp=''
set @tmp = left(@temp,charindex('-',@temp)-2)
set @min = cast(right(left(@temp,charindex('-',@temp)-1),1) as int)
set @max = cast(right(@temp,len(@temp)-charindex('-',@temp)) as int)
if @min > @max
begin
set @memo=@memo+@tmp+cast(@min as varchar(1000))+' '
set @memo=@memo+@tmp+cast(@max as varchar(1000))+' '
end
else
begin
while (@min <= @max)
begin
set @memo=@memo+@tmp+cast(@min as varchar(1000))+' '
set @min=@min+1
end
end
end
set @string=right(@string,len(@string)-@len)
set @len = charindex(',',@string)
if @len=0
set @memo=@memo+@string
end
print @memo/*
7856321 7856320 7936783 7936784 7936785 7936786 7936787 7936788 7936789 7936799
*/