编写一个类似于这样的函数吧
/*
作者:贾桂军
日期:2009-04-01
作用:根据指定拆分字符,对字符串进行拆分
调用:select Split from fun_Split(',','1,2,3')
*/
ALTER function [dbo].[fun_Split]
(@Symbol varchar(100),
@SplitStr varchar(8000))
returns @t table(Split varchar(10))
begin
declare @i int
set @i=charindex(@Symbol,@SplitStr)
while @i<>0
begin
insert @t values(left(@SplitStr,@i-1))
set @SplitStr=right(@SplitStr,len(@SplitStr)-@i)
set @i=charindex(@Symbol,@SplitStr)
end
insert @t values(@SplitStr)
return
end
/*
作者:贾桂军
日期:2009-04-01
作用:根据指定拆分字符,对字符串进行拆分
调用:select Split from fun_Split(',','1,2,3')
*/
ALTER function [dbo].[fun_Split]
(@Symbol varchar(100),
@SplitStr varchar(8000))
returns @t table(Split varchar(10))
begin
declare @i int
set @i=charindex(@Symbol,@SplitStr)
while @i<>0
begin
insert @t values(left(@SplitStr,@i-1))
set @SplitStr=right(@SplitStr,len(@SplitStr)-@i)
set @i=charindex(@Symbol,@SplitStr)
end
insert @t values(@SplitStr)
return
end
set @taoistong='XS,12'
select substring(@taoistong,1,CHARINDEX(',',@taoistong)-2),substring(@taoistong,CHARINDEX(',',@taoistong)+1,len(@taoistong)-CHARINDEX(',',@taoistong))可以的
需要拆分的字段是SIZE
应该怎么来这个函数呢?
谢谢!
SELECT
LEFT(sizeqty,CHARINDEX(',',sizeqty)-1) AS size,
RIGHT(sizeqty,CHARINDEX(',',REVERSE(sizeqty))-1) AS qty
FROM (
SELECT
SUBSTRING(A.sizeqty,B.number,CHARINDEX(';',A.sizeqty+';',B.number)-B.number) AS sizeqty
FROM tb AS A
JOIN master.dbo.spt_values AS B
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.sizeqty)
AND SUBSTRING(';'+A.sizeqty,B.number,1)=';'
) AS A
INSERT INTO #tab1 values(1,'1,2')
insert into #tab1 values(2,'2,3,5')
insert into #tab1 values(3,'6')
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
select * from #
/*
先看substring的条件当aID为1的时候那么b就为1,2
此时在看substring(',1,2',1,1)=','条件成立,那么此时b.id=1
在看CHARINDEX(',','1,2,',1)结果为2减去b.id的1,在看substring(1,'1,2',1)那就是1
单d.id为2的时候看substring(',1,2',2,1)=1而不是','所以不满足,在看3满足条件
*/
SELECT A.aID, SUBSTRING(A.b, B.id, CHARINDEX(',', A.b + ',', B.id) - B.id)
FROM tab1 A, # B
WHERE SUBSTRING(',' + A.b, B.id, 1) = ','
INSERT @tb SELECT 'XS,12; S,24; M,6; L,6'
UNION ALL SELECT '38,30;40,48;42,30;'
UNION ALL SELECT 'OS,18;'SELECT
LEFT(sizeqty,CHARINDEX(',',sizeqty)-1) AS size,
RIGHT(sizeqty,CHARINDEX(',',REVERSE(sizeqty))-1) AS qty
FROM (
SELECT
SUBSTRING(A.sizeqty,B.number,CHARINDEX(';',A.sizeqty+';',B.number)-B.number) AS sizeqty
FROM @tb AS A
JOIN master.dbo.spt_values AS B
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.sizeqty)
AND SUBSTRING(';'+A.sizeqty,B.number,1)=';'
) AS A