如题所述
createfunctionf_split(@cvarchar(2000),@splitvarchar(2))
returns@ttable(colvarchar(20))
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),'')
end
insert@t(col)values(@c)
return
end
go
select*fromdbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',')
dropfunctionf_split
col
--------------------
dfkd
dfdkdf
dfdkf
dffjk
扩展资料
巧用SQL内置函数分割字符串
createfunction[dbo].[fn_char_splitbystr](
@Stringnvarchar(4000),function
@Delimiternvarchar(100)
)
returns@ValueTabletable([FValue]nvarchar(4000),[FSerial]int)
as
begin
declare@valuenvarchar(4000),@valnvarchar(4000)
declare@NextStringnvarchar(4000),@Posint,@NextPosint,@CommaChecknvarchar(1),@idint
set@value=@String
set@id=1
set@NextString=''
set@CommaCheck=right(@value,1)
set@value=@value+@Delimiter+space(len(@Delimiter)-1)
set@Pos=charindex(@Delimiter,@value)
set@NextPos=1
while(@pos<>0)begin
set@NextString=substring(@value,1,@Pos-1)
set@val=@NextString
if@id>1set@val=substring(@val,len(@Delimiter),len(@val))
insertinto@ValueTable([FValue],[FSerial])VALUES(@val,@id)
set@value=substring(@value,@pos+1,LEN(@value))
set@NextPos=@Pos
set@pos=charindex(@Delimiter,@value)
set@id=@id+1
end
return
end
CREATE OR REPLACE TYPE split_type IS TABLE OF VARCHAR2 (4000) /（2）定义split函数：
CREATE OR REPLACE FUNCTION split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN split_type IS j INT := 0; i INT := 1; len INT := 0; len1 INT := 0; str VARCHAR2 (4000); my_split split_type := split_type (); BEGIN len := LENGTH (p_str); len1 := LENGTH (p_delimiter); WHILE j < len LOOP j := INSTR (p_str, p_delimiter, i); IF j = 0 THEN j := len; str := SUBSTR (p_str, i); my_split.EXTEND; my_split (my_split.COUNT) := str; IF i >= len THEN EXIT; END IF; ELSE str := SUBSTR (p_str, i, j - i); i := j + len1; my_split.EXTEND; my_split (my_split.COUNT) := str; END IF; END LOOP; RETURN my_split; END split; /（3）存储过程中，使用类似本回答被网友采纳
