SQL Server 完美SPLIT函数
--SQL Server Split函数--Author:zc_0101
--说明：
--支持分割符多字节
--使用方法
--Select * FROM DBO.F_SQLSERVER_SPLIT('1203401230105045','0')
--select * from DBO.F_SQLSERVER_SPLIT('abc1234a12348991234','1234')
--Select * from DBO.F_SQLSERVER_SPLIT('ABC',',')
1 CREATE FUNCTION F_SQLSERVER_SPLIT(@Long_str NVARCHAR(MAX),@split_str NVARCHAR(100))
2 RETURNS@tmp TABLE(
3 ID inT IDENTITY PRIMARY KEY,
4 short_str NVARCHAR(MAX)
5 )
6 AS
7 BEGIN
8 DECLARE @short_str NVARCHAR(MAX),@split_str_length int,@split_str_Position_Begin int
9 SET @split_str_length = LEN(@split_str)
10 SET @Long_str=REPLACE(REPLACE(@Long_str,CHAR(10),''),CHAR(13),'')
11 IF CHARINDEX(@split_str,@Long_str)=1
12 SET @Long_str=STUFF(@Long_str,1,@split_str_length,'')
13 IF CHARINDEX(@split_str,@Long_str)=0
14 INSERT INTO @tmp SELECT @Long_str
15 ELSE
16 BEGIN
17 WHILE 1>0
18 BEGIN
19 SET @split_str_Position_Begin = CHARINDEX(@split_str,@Long_str)
20 SET @short_str=LEFT(@Long_str,@split_str_Position_Begin-1)
21 IF @short_str'' INSERT INTO @tmp SELECT @short_str
22 SET @Long_str=STUFF(@Long_str,1,@split_str_Position_Begin+@split_str_length-1,'')
23 SET @split_str_Position_Begin = CHARINDEX(@split_str,@Long_str)
24 IF @split_str_Position_Begin=0
25 BEGIN
26 IF LTRIM(@Long_str)''
27 INSERT INTO @tmp SELECT @Long_str
28 BREAK
29 END
30 END
31 END
32 RETURN
33 END
页:
[1]