27,580
社区成员
发帖
与我相关
我的任务
分享SQL 分割字符串
STR="A00,A03-A06,B00,C00-C03"
我想到到结果为
STR
------------
A00
A03
A04
A05
A06
B00
C00
C01
C02
C03
我想到到结果为
STR
------------
A00
A03
A04
A05
A06
B00
C00
C01
C02
C03
...全文
请发表友善的回复…
发表回复
marongc 2013-08-21
- 打赏
- 举报
采用 唐诗三百首 其它平均给分,谢谢大家.
marongc 2013-08-21
- 打赏
- 举报
昨天到现在才上网,先谢谢大家,我消化一下.
谢谢各位了...
lzw_0736 2013-08-20
- 打赏
- 举报
WITH a1 (cstr) AS
(SELECT 'A00,A03-A06,B00,C00-C03')
,a2 AS
(SELECT ','+REPLACE(cstr,'-',',')+',' cstr FROM a1)
,a3 AS
(
SELECT cstr,CHARINDEX(',',cstr,1) n1,1 n2
FROM a2
UNION ALL
SELECT cstr,CHARINDEX(',',cstr,n1+1) n1,n2+1
FROM a3
WHERE CHARINDEX(',',cstr,n1+1)>0
)
,a4 AS
(SELECT cstr,n1,(SELECT n1 FROM a3 WHERE cstr=a.cstr AND n2=a.n2+1) n2 FROM a3 a)
SELECT SUBSTRING(cstr,n1+1,n2-n1-1) FROM a4 WHERE n2 IS NOT null
ali_mushishi 2013-08-20
- 打赏
- 举报
好强大的样子
唐诗三百首 2013-08-20
- 打赏
- 举报
declare @str varchar(100)
select @str='A00,A03-A06,B00,C00-C03'
;with t as
(select substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number) 'STR'
from (select @str 's') a,master.dbo.spt_values b
where b.[type]='p' and b.number between 1 and len(a.s)
and substring(','+a.s,b.number,1)=','
)
select [STR] from t where charindex('-',[STR],1)=0
union all
select b.[STR]
from t cross apply
(select left(t.[STR],1)+replicate('0',2-len(b.number))+rtrim(b.number) 'STR'
from master.dbo.spt_values b where b.[type]='p'
and b.number between cast(substring(t.[STR],2,2) as int)
and cast(substring(t.[STR],6,2) as int)) b
where charindex('-',t.[STR],1)>0
order by [STR]
/*
STR
--------------------
A00
A03
A04
A05
A06
B00
C00
C01
C02
C03
(10 row(s) affected)
*/
再来壹串 2013-08-20
- 打赏
- 举报
SELECT 'A00,A03-A06,B00,C00-C03' AS id INTO #a
SELECT * FROM #a
SELECT * INTO #b FROM dbo.split2( (SELECT id FROM #a),',')
SELECT * FROM #b
SELECT *,ROW_NUMBER()OVER(ORDER BY VALUE) AS row INTO #c FROM #b WHERE CHARINDEX('-',VALUE) >0
SELECT * FROM #c
go
CREATE TABLE #result
(
val NVARCHAR(10)
)
go
INSERT INTO #result(val)
SELECT * FROM #b WHERE CHARINDEX('-',VALUE) =0
go
go
DECLARE @i INT=1 ,@t INT,@val NVARCHAR(10)
SELECT @t=COUNT(1) FROM #c
WHILE @i <=@t
BEGIN
DECLARE @begin INT,@end INT,@temp NVARCHAR(10)
SELECT @val=value FROM #c WHERE row =@i
IF CHARINDEX('-',@val)<>0
BEGIN
SELECT @begin = SUBSTRING(SUBSTRING(@val,0,CHARINDEX('-',@val)),2,10)
SELECT @end = SUBSTRING(SUBSTRING(@val,CHARINDEX('-',@val)+1,10),2,10)
IF @begin > @end RETURN
WHILE @begin <= @end
BEGIN
SELECT @temp = CONVERT(NVARCHAR(10),@begin)
IF LEN(@temp)=1 SET @temp = '0'+@temp
INSERT INTO #result(val)
SELECT SUBSTRING(@val,1,1) + @temp
SET @begin = @begin+1
END
END
SET @i = @i +1
END
SELECT * FROM #result ORDER BY val
val
----------
A00
A03
A04
A05
A06
B00
C00
C01
C02
C03
(10 行受影响)
再来壹串 2013-08-20
- 打赏
- 举报
谢谢,但是结果和我想要的不一样哦[/quote]
再来一个
再来一个
--小F-- 2013-08-20
- 打赏
- 举报
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date :2003-10-14
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
select * from dbo.f_split('1,2,3,4',',')
a
--------------------
1
2
3marongc 2013-08-20
- 打赏
- 举报
谢谢,请问可以再简单点吗,比如说不用XML的?
marongc 2013-08-20
- 打赏
- 举报
谢谢,但是结果和我想要的不一样哦
Shawn 2013-08-20
- 打赏
- 举报
DECLARE @string VARCHAR(100)
SET @string = 'A00,A03-A06,B00,C00-C03'
;WITH cte AS
(
SELECT
rowid=ROW_NUMBER() OVER(ORDER BY GETDATE()),
ch = LEFT(B.string, 1),
beginstr = STUFF(LEFT(B.string, LEN(B.string)-CHARINDEX('-', B.string)),1,1,''),
endstr = STUFF(SUBSTRING(B.string, CHARINDEX('-', B.string)+1,LEN(B.string)),1,1,'')
FROM
(SELECT string=CONVERT(XML, '<root><v>'+replace(@string,',','</v><v>')+'</v></root>')) a
OUTER APPLY
(SELECT string = C.v.value('.','NVARCHAR(MAX)') FROM a.string.nodes('/root/v') C(v)) b
)
SELECT
CH = ch + RIGHT('00'+LTRIM((CAST(beginstr AS INT)+ISNULL(number,0))), 2)
FROM cte a
OUTER APPLY
(
SELECT number FROM master..spt_values WHERE type = 'p' AND number <= CAST(a.endstr AS INT)-CAST(a.beginstr AS INT)
) b
ORDER BY a.rowid
/*
CH
A00
A03
A04
A05
A06
B00
C00
C01
C02
C03
*/再来壹串 2013-08-20
- 打赏
- 举报
ALTER FUNCTION split2
(
@SplitString NVARCHAR(999) ,--需要被分隔的字符串
@Separator NVARCHAR(10)--分隔符
)
RETURNS @SplitTable TABLE
(
[value] NVARCHAR(999)
)
AS
BEGIN
DECLARE @CurrentIndex INT=1,@NextIndex INT,@ReturnText NVARCHAR(999)
WHILE ( @CurrentIndex <= LEN(@SplitString) )
BEGIN
SELECT @NextIndex = CHARINDEX(@Separator, @SplitString,@CurrentIndex)
IF ( @NextIndex = 0 OR @NextIndex IS NULL )
SELECT @NextIndex = LEN(@SplitString) + 1
SELECT @ReturnText = SUBSTRING(@SplitString, @CurrentIndex,@NextIndex - @CurrentIndex)
INSERT INTO @SplitTable( [value] ) VALUES ( @ReturnText )
SELECT @CurrentIndex = @NextIndex + 1 ;
END
RETURN
END
SELECT * FROM dbo.SPLIT2(
(SELECT REPLACE(REPLACE(id,',',' '),'-',' ')FROM #a)
,' '
)
value
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A00
A03
A06
B00
C00
C03
(6 行受影响)
