SQL Server数据库中拆分字符串函数的具体方法
CREATE FUNCTION uf_StrSplit
(@origStr varchar() 待拆分的字符串
@markStr varchar()) 拆分标记如
RETURNS @splittable table
(
str_id varchar() NOT NULL 编号ID
string varchar() NOT NULL 拆分后的字符串
)
AS
BEGIN
declare @strlen int@postion int@start int@sublen int
@TEMPstr varchar()@TEMPid int
SELECT @strlen=LEN(@origStr)@start=@sublen=@postion=
@TEMPstr=@TEMPid=
if(RIGHT(@origStr)<>@markStr )
begin
set @origStr = @origStr + @markStr
end
WHILE((@postion<=@strlen) and (@postion !=))
BEGIN
IF(CHARINDEX(@markStr@origStr@postion)!=)
BEGIN
SET @sublen=CHARINDEX(@markStr@origStr@postion)@postion;
END
ELSE
BEGIN
SET @sublen=@strlen@postion+;
END
IF(@postion<=@strlen)
BEGIN
SET @TEMPid=@TEMPid+;
SET @TEMPstr=SUBSTRING(@origStr@postion@sublen);
INSERT INTO @splittable(str_idstring)
values(@TEMPid@TEMPstr)
IF(CHARINDEX(@markStr@origStr@postion)!=)
BEGIN
SET @postion=CHARINDEX(@markStr@origStr@postion)+
END
ELSE
BEGIN
SET @postion=@postion+
END
END
END
RETURN
END
例如select * from uf_StrSplit()
输出结果
str_id string
