怎么样用一个SQL将字符串按逗号分开

小破孩子

SQL code --各种字符串分函数

if
exists (select
*
from dbo.sysobjects where id =
object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))drop
function
[dbo].[f_splitSTR]
GO
--3.2.1 循环截取法
CREATE
FUNCTION f_splitSTR(@s
varchar(8000),   --待分拆的字符串
@split
varchar(10)     --数据分隔符
)RETURNS
@re
TABLE(col varchar(100))AS
BEGIN
DECLARE
@splitlen
int
SET
@splitlen=LEN(@split+'a')-2
WHILE
CHARINDEX(@split,@s)>0
BEGIN
INSERT
@re
VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))        SET
@s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')    END
INSERT
@re
VALUES(@s)    RETURN
END
GO
/*==============================================*/
if
exists (select
*
from dbo.sysobjects where id =
object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))drop
function
[dbo].[f_splitSTR]
GO
--3.2.3.1 使用临时性分拆辅助表法
CREATE
FUNCTION f_splitSTR(@s
varchar(8000),  --待分拆的字符串
@split
varchar(10)     --数据分隔符
)RETURNS
@re
TABLE(col varchar(100))AS
BEGIN
--创建分拆处理的辅助表(用户定义函数中只能操作表变量)

DECLARE
@t
TABLE(ID int
IDENTITY,b bit)    INSERT
@t(b) SELECT
TOP
8000
0
FROM syscolumns a,syscolumns b    INSERT
@re
SELECT
SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)    FROM
@t
WHERE ID<=LEN(@s+'a')         AND
CHARINDEX(@split,@split+@s,ID)=ID    RETURN
END
GO
/*==============================================*/
if
exists (select
*
from dbo.sysobjects where id =
object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))drop
function
[dbo].[f_splitSTR]
GO
if
exists (select
*
from dbo.sysobjects where id =
object_id(N'[dbo].[tb_splitSTR]') and
objectproperty(id,N'IsUserTable')=1)drop
table
[dbo].[tb_splitSTR]
GO
--3.2.3.2 使用永久性分拆辅助表法--字符串分拆辅助表
SELECT
TOP
8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTRFROM syscolumns a,syscolumns bGO
--字符串分拆处理函数
CREATE
FUNCTION f_splitSTR(@s
varchar(8000),  --待分拆的字符串
@split
varchar(10)     --数据分隔符
)RETURNS
TABLE
AS
RETURN(    SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as
varchar(100))    FROM tb_splitSTR    WHERE ID<=LEN(@s+'a')         AND
CHARINDEX(@split,@split+@s,ID)=ID)GO
/*==============================================*/
if
exists (select
*
from dbo.sysobjects where id =
object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))drop
function
[dbo].[f_splitSTR]
GO
--3.2.5 将数据项按数字与非数字再次拆份
CREATE
FUNCTION f_splitSTR(@s
varchar(8000),    --待分拆的字符串
@split
varchar(10)     --数据分隔符
)RETURNS
@re
TABLE(No varchar(100),Value varchar(20))AS
BEGIN
--创建分拆处理的辅助表(用户定义函数中只能操作表变量)

DECLARE
@t
TABLE(ID int
IDENTITY,b bit)    INSERT
@t(b) SELECT
TOP
8000
0
FROM syscolumns a,syscolumns b    INSERT
@re
SELECT    No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')),        Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))    FROM(        SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))        FROM
@t
WHERE ID<=LEN(@s+'a')             AND
CHARINDEX(@split,@split+@s,ID)=ID)a    RETURN
END
GO
/*==============================================*/
if
exists (select
*
from dbo.sysobjects where id =
object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))drop
function
[dbo].[f_splitSTR]
GO
--3.2.6 分拆短信数据
CREATE
FUNCTION f_splitSTR(@s
varchar(8000))RETURNS
@re
TABLE(split varchar(10),value varchar(100))AS
BEGIN
DECLARE
@splits
TABLE(split varchar(10),splitlen as
LEN(split))    INSERT
@splits(split)    SELECT
'AC'
UNION
ALL
SELECT
'BC'
UNION
ALL
SELECT
'CC'
UNION
ALL
SELECT
'DC'
DECLARE
@pos1
int,@pos2
int,@split
varchar(10),@splitlen
int
SELECT
TOP
1
@pos1=1,@split=split,@splitlen=splitlen    FROM
@splits
WHERE
@s
LIKE split+'%'
WHILE
@pos1>0
BEGIN
SELECT
TOP
1
@pos2=CHARINDEX(split,@s,@splitlen+1)        FROM
@splits
WHERE
CHARINDEX(split,@s,@splitlen+1)>0
ORDER
BY
CHARINDEX(split,@s,@splitlen+1)        IF
@@ROWCOUNT=0
BEGIN
INSERT
@re
VALUES(@split,STUFF(@s,1,@splitlen,''))            RETURN
END
ELSE
BEGIN
INSERT
@re
VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1))            SELECT
TOP
1
@pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'')            FROM
@splits
WHERE
STUFF(@s,1,@pos2-1,'') LIKE split+'%'
END
END
RETURN
END
GO

小破孩子

declare @sql varchar(5000),@totalLength int,@length int,@tag varchar(20),@currentNumber int

set @sql='baidu,google,yahoo,my,db,sqlserver,helloworld,'
set @tag=','
set @totalLength=len(@sql)

set @length=charindex(@tag,@sql)
print '第1条记录: '+left(@sql,@length-1)
set @sql=substring(@sql,@length+1,@totalLength)
set @currentNumber=1
while (@length>0)
begin
set @currentNumber=@currentNumber+1
set @length=charindex(@tag,@sql)
if(@length=0)
begin
break;
end
print '第'+ltrim(str(@currentNumber))+'条记录:'+left(@sql,@length-1)
set @sql=substring(@sql,@length+1,@totalLength)
end


第1条记录: baidu
第2条记录:google
第3条记录:yahoo
第4条记录:my
第5条记录:db
第6条记录:sqlserver
第7条记录:helloworld

小破孩子

如果是固定长度隔开的
可以参考这个，这个是6位用,隔开的

SELECT     TO_NUMBER
                                          (DECODE
                                              (LENGTH (in_promo_id),
                                               6, in_promo_id,
                                               SUBSTR
                                                  (in_promo_id,
                                                   DECODE
                                                         (ROWNUM,
                                                          1, 1,
                                                            INSTR
                                                                 (in_promo_id,
                                                                  ',',
                                                                  1,
                                                                  ROWNUM - 1
                                                                 )
                                                          + 1
                                                         ),
                                                   6
                                                  )
                                              )
                                          ) promo_id
                                  FROM DUAL
                            CONNECT BY ROWNUM <=
                                            LENGTH (in_promo_id)
                                          - LENGTH (REPLACE (in_promo_id,
                                                             ',',
                                                             ''
                                                            )
                                                   )
                                          + 1

小破孩子

传进来的in_promo_id
格式如下
可能是100000
也有可能是100001,100002,100004

出来的列名为promo_id

因为转出来的要转换成数字，不需要转换的话，就把to_number去掉
长度固定，但不是6的话，把语句里所有6改成你的长度

如果是不固定长度的，稍微麻烦点

其实关键就是substr 和instr配合起来截取，用instr找到起始位置，然后再用instr来算出要截取的长度
就是要注意第一个和最后个

小破孩子

哎,可惜我的不是固定长度的

愁死人

小破孩子

又弄来一个方法

/*create by yinzl
*date: 2008-06-20
*version:1.0
*use to: split string splited by specific sybmblic
*company: Linkage Co.Ltd
*/

--创建一个type，如果为了使split函数具有通用性，请将其size 设大些。
create or replace type type_split as table of varchar2(50);

--创建function
create or replace function split
(
   p_list varchar2,
   p_sep varchar2 := ','
)  return type_split pipelined
is
   l_idx  pls_integer;
   v_list  varchar2(50) := p_list;
begin
   loop
      l_idx := instr(v_list,p_sep);
      if l_idx > 0 then
          pipe row(substr(v_list,1,l_idx-1));
          v_list := substr(v_list,l_idx+length(p_sep));
      else
          pipe row(v_list);
          exit;
      end if;
   end loop;
   return;
end split;



测试　select * from table(split(你的字串））

select * from table(split('123,234',','))

小破孩子

SELECT (DECODE(LENGTH(dd.instrument_number),
               14,
               dd.instrument_number,
               SUBSTR(dd.instrument_number,
                   DECODE(ROWNUM,
                            1,
                           1,
                           INSTR(dd.instrument_number,
                                 ',',
                                 1,
                                  ROWNUM - 1) + 1),
                      14))) instrument_number
  FROM log_ops_record_detl dd
CONNECT BY ROWNUM <= LENGTH(dd.instrument_number) -
LENGTH(REPLACE(dd.instrument_number, ',', '')) + 1