SQL按指定字符拆分字符串函数
CREATE FUNCTION Split(
@Str VARCHAR(MAX),
@Separator VARCHAR(10)
)
RETURNS@Res TABLE( Strs VARCHAR(MAX))
AS
BEGIN
DECLARE@Index INT
DECLARE@StartPoint INT
DECLARE@LEN INT
DECLARE@Flag BIT
SET@Flag=1
SET@Index=0
WHILE@Flag<>0
BEGIN
SET@StartPoint=@Index+1
SET@Index=CHARINDEX(@Separator,@Str,@StartPoint)
SET@LEN=@Index-@StartPoint
IF@Index=0
BEGIN
SET@Flag=0
SET@LEN=LEN(@Str)-@StartPoint+1
END
INSERT INTO@Res VALUES(SUBSTRING(@Str,@StartPoint,@LEN))
END
RETURN
END
SELECT*FROM Split('A,B,CD,DF',',')
存储过程
CREATE PROCEDURE[dbo].[SplitStr]
@Str VARCHAR(MAX),
@Separator VARCHAR(10)
AS
BEGIN
DECLARE@Index INT
DECLARE@StartPoint INT
DECLARE@LEN INT
DECLARE@T TABLE(
Strs VARCHAR(MAX)
)
字符串截取 sqlDECLARE@Flag BIT
SET@Flag=1
SET@Index=0
WHILE@Flag<>0
BEGIN
SET@StartPoint=@Index+1
SET@Index=CHARINDEX(@Separator,@Str,@StartPoint)
SET@LEN=@Index-@StartPoint
IF@Index=0
BEGIN
SET@Flag=0
SET@LEN=LEN(@Str)-@StartPoint+1
END
INSERT INTO@T VALUES(SUBSTRING(@Str,@StartPoint,@LEN))
END
SELECT*FROM@T
END
EXEC SplitStr 'A,B,CD,EFG',','

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。