怎么样用一个SQL将字符串按逗号分开
SQL code --各种字符串分函数if
exists (select
*
from dbo.sysobjects where id =
object_id(N'.') 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'.') 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'.') 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'.') 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'.') 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'.') 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_idxpls_integer;
v_listvarchar2(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
页:
[1]