在java中
String str = "111999991,222,333,444 ";
经过转换,可以将字符串分成以下几个字符串
tests [0] = 111999991
tests [1] = 222
tests [2] = 333
tests [3] = 444 不知道在sql server 中怎么将一个字符串按 ', '分成多个字符串,还请个位大侠指教!
String str = "111999991,222,333,444 ";
经过转换,可以将字符串分成以下几个字符串
tests [0] = 111999991
tests [1] = 222
tests [2] = 333
tests [3] = 444 不知道在sql server 中怎么将一个字符串按 ', '分成多个字符串,还请个位大侠指教!
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(200))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
-- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))
end
insert @t(col) values (@c)
return
end
/*测试
select * from dbo.m_split('1,2,3,4,5',',')
*/
/*
col
----------
1
2
3
4
5
*/
declare @sql varchar(100)
set @sql= '111999991,222,333,444'select [str] = substring(a.s , b.number , charindex(',' , a.s + ',' , b.number) - b.number)
from (select @sql as s) a join master..spt_values b
on b.type='p' and b.number between 1 and len(a.s)
where substring(',' + a.s , b.number , 1) = ','
declare @sql varchar(1000)
set @sql = 'AAAAAAAAAAA,BBBBBBBBB,CCCCCCCCCCC,DDDDDDDDD'
set @sql = 'select ''' + replace(@sql, ',', ''',''') + ''''
exec(@sql)
select @delimeter=','
,@str = '111999991,222,333,444'
;
WITH
Tables as
(
select 1 as ID,@Str as Title
)
,cte AS
(
SELECT ID, 1 as Pos,1 as StartPos,CHARINDEX(@Delimeter,Title+@Delimeter)-1 as EndPos
FROM Tables
UNION ALL
SELECT cte.ID,cte.Pos+1,cte.EndPos+2,CHARINDEX(@Delimeter,fs.Title+@Delimeter,cte.endpos+2)-1
FROM cte JOIN
Tables fs ON cte.ID=fs.ID AND CHARINDEX(@Delimeter,fs.Title+@Delimeter,cte.endpos+2)>0
)
SELECT --fs.ID,fs.Title,cte.Pos,cte.StartPos,cte.EndPos
SUBSTRING(fs.title,startpos,endpos-startpos+1) as Title
FROM Tables fs join
cte on fs.id=cte.id
order by fs.id,pos
;
/*
Title
---------
111999991
222
333
444
*/
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
3
ALTER procedure [dbo].[proc_fa_checkequipid]
@equip_ids varchar(max)
as
-- 检测 t_input是否存在equip_id 以字符串形式返回
declare @T table (Equip_id varchar(20))
declare @i int -- 计数器
declare @count int -- 计数器
declare @outputchar varchar(max)
declare c1 cursor for select equip_id from @T
begin
set @i = 1
set @outputchar = ''
while charindex(',',@equip_ids) > 0
begin
insert into @T values( substring(@equip_ids,1,charindex(',',@equip_ids)-1))
set @equip_ids = substring(@equip_ids,charindex(',',@equip_ids)+1,len(@equip_ids))
set @i = @i + 1
end
insert into @T values(@equip_ids)
select * from @T;
end
create procedure [dbo].[sp_split]
@equip_ids varchar(max)
as
declare @T table (Equip_id varchar(20))
declare @i int -- 计数器
begin
set @i = 1
set @outputchar = ''
while charindex(',',@equip_ids) > 0
begin
insert into @T values( substring(@equip_ids,1,charindex(',',@equip_ids)-1))
set @equip_ids = substring(@equip_ids,charindex(',',@equip_ids)+1,len(@equip_ids))
set @i = @i + 1
end
insert into @T values(@equip_ids)
select * from @T;
end