--各种字符串分函数if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.3.1 使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
DECLARE @t TABLE(ID int IDENTITY,b bit)
INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
FROM @t
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(@split,@split+@s,ID)=ID
RETURN
END
GO/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)
drop table [dbo].[tb_splitSTR]
GO--3.2.3.2 使用永久性分拆辅助表法
--字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO--字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS TABLE
AS
RETURN(
SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
FROM tb_splitSTR
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(@split,@split+@s,ID)=ID)
GO
/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.5 将数据项按数字与非数字再次拆份
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(No varchar(100),Value varchar(20))
AS
BEGIN
--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
DECLARE @t TABLE(ID int IDENTITY,b bit)
INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b INSERT @re
SELECT No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')),
Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))
FROM(
SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
FROM @t
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(@split,@split+@s,ID)=ID)a
RETURN
END
GO
/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.6 分拆短信数据
CREATE FUNCTION f_splitSTR(@s varchar(8000))
RETURNS @re TABLE(split varchar(10),value varchar(100))
AS
BEGIN
DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split))
INSERT @splits(split)
SELECT 'AC' UNION ALL
SELECT 'BC' UNION ALL
SELECT 'CC' UNION ALL
SELECT 'DC'
DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int
SELECT TOP 1
@pos1=1,@split=split,@splitlen=splitlen
FROM @splits
WHERE @s LIKE split+'%'
WHILE @pos1>0
BEGIN
SELECT TOP 1
@pos2=CHARINDEX(split,@s,@splitlen+1)
FROM @splits
WHERE CHARINDEX(split,@s,@splitlen+1)>0
ORDER BY CHARINDEX(split,@s,@splitlen+1)
IF @@ROWCOUNT=0
BEGIN
INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,''))
RETURN
END
ELSE
BEGIN
INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1))
SELECT TOP 1
@pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'')
FROM @splits
WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%'
END
END
RETURN
END
GO
drop function [dbo].[f_splitSTR]
GO--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.3.1 使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
DECLARE @t TABLE(ID int IDENTITY,b bit)
INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
FROM @t
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(@split,@split+@s,ID)=ID
RETURN
END
GO/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)
drop table [dbo].[tb_splitSTR]
GO--3.2.3.2 使用永久性分拆辅助表法
--字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO--字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS TABLE
AS
RETURN(
SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
FROM tb_splitSTR
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(@split,@split+@s,ID)=ID)
GO
/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.5 将数据项按数字与非数字再次拆份
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(No varchar(100),Value varchar(20))
AS
BEGIN
--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
DECLARE @t TABLE(ID int IDENTITY,b bit)
INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b INSERT @re
SELECT No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')),
Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))
FROM(
SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
FROM @t
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(@split,@split+@s,ID)=ID)a
RETURN
END
GO
/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.6 分拆短信数据
CREATE FUNCTION f_splitSTR(@s varchar(8000))
RETURNS @re TABLE(split varchar(10),value varchar(100))
AS
BEGIN
DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split))
INSERT @splits(split)
SELECT 'AC' UNION ALL
SELECT 'BC' UNION ALL
SELECT 'CC' UNION ALL
SELECT 'DC'
DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int
SELECT TOP 1
@pos1=1,@split=split,@splitlen=splitlen
FROM @splits
WHERE @s LIKE split+'%'
WHILE @pos1>0
BEGIN
SELECT TOP 1
@pos2=CHARINDEX(split,@s,@splitlen+1)
FROM @splits
WHERE CHARINDEX(split,@s,@splitlen+1)>0
ORDER BY CHARINDEX(split,@s,@splitlen+1)
IF @@ROWCOUNT=0
BEGIN
INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,''))
RETURN
END
ELSE
BEGIN
INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1))
SELECT TOP 1
@pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'')
FROM @splits
WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%'
END
END
RETURN
END
GO
insert into tb values('1234abdsf', '000000000001-000000000020', 'CT001', 20)
insert into tb values('3242121sf', '000000000021-000000000050', 'CT002', 30)
go
--使用一临时表
SELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO tmp FROM syscolumns a, syscolumns b select m.id , Number = right('000000000000' + cast(cast(left(number,charindex('-',number)-1) as bigint) + n.id as varchar),12) , typeid , [count] = 1
from tb m, tmp n
where cast(left(number,charindex('-',number)-1) as bigint) + + n.id <= cast(substring(number,charindex('-',number)+1,len(number)) as bigint)
order by m.id drop table tb,tmp/*
id Number typeid count
---------- ------------------------ ---------- -----------
1234abdsf 000000000001 CT001 1
1234abdsf 000000000002 CT001 1
1234abdsf 000000000003 CT001 1
1234abdsf 000000000004 CT001 1
1234abdsf 000000000005 CT001 1
1234abdsf 000000000006 CT001 1
1234abdsf 000000000007 CT001 1
1234abdsf 000000000008 CT001 1
1234abdsf 000000000009 CT001 1
1234abdsf 000000000010 CT001 1
1234abdsf 000000000011 CT001 1
1234abdsf 000000000012 CT001 1
1234abdsf 000000000013 CT001 1
1234abdsf 000000000014 CT001 1
1234abdsf 000000000015 CT001 1
1234abdsf 000000000016 CT001 1
1234abdsf 000000000017 CT001 1
1234abdsf 000000000018 CT001 1
1234abdsf 000000000019 CT001 1
1234abdsf 000000000020 CT001 1
3242121sf 000000000021 CT002 1
3242121sf 000000000022 CT002 1
3242121sf 000000000023 CT002 1
3242121sf 000000000024 CT002 1
3242121sf 000000000025 CT002 1
3242121sf 000000000026 CT002 1
3242121sf 000000000027 CT002 1
3242121sf 000000000028 CT002 1
3242121sf 000000000029 CT002 1
3242121sf 000000000030 CT002 1
3242121sf 000000000031 CT002 1
3242121sf 000000000032 CT002 1
3242121sf 000000000033 CT002 1
3242121sf 000000000034 CT002 1
3242121sf 000000000035 CT002 1
3242121sf 000000000036 CT002 1
3242121sf 000000000037 CT002 1
3242121sf 000000000038 CT002 1
3242121sf 000000000039 CT002 1
3242121sf 000000000040 CT002 1
3242121sf 000000000041 CT002 1
3242121sf 000000000042 CT002 1
3242121sf 000000000043 CT002 1
3242121sf 000000000044 CT002 1
3242121sf 000000000045 CT002 1
3242121sf 000000000046 CT002 1
3242121sf 000000000047 CT002 1
3242121sf 000000000048 CT002 1
3242121sf 000000000049 CT002 1
3242121sf 000000000050 CT002 1(所影响的行数为 50 行)*/
insert into tb values('1234abdsf', '000000000001-000000000020', 'CT001', 20)
insert into tb values('3242121sf', '000000000021-000000000050', 'CT002', 30)
go
--使用一临时表
SELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO tmp FROM syscolumns a, syscolumns b select m.id , Number = right('000000000000' + cast(cast(left(number,charindex('-',number)-1) as bigint) + n.id as varchar),12) , typeid , [count] = 1
from tb m, tmp n
where cast(left(number,charindex('-',number)-1) as bigint) + + n.id <= cast(substring(number,charindex('-',number)+1,len(number)) as bigint)
order by m.id drop table tb,tmp
insert into tb values('1234abdsf', '000000000001-000000000020', 'CT001', 20)
insert into tb values('3242121sf', '000000000021-000000000050', 'CT002', 30)
goselect top 1000 id=identity(int,1,1) into # from sysobjects a,sysobjects bselect a.id,right('000000000000'+ltrim(cast(substring(number,1,charindex('-',number)-1) as int)+b.id-1),12) as number,typeid,[count]=1 from tb a,# b
where cast(substring(number,1,charindex('-',number)-1) as int)+b.id-1<=cast(substring(number,charindex('-',number)+1,len(number)) as int)
ID Number TypeID Count
324fs21sf 000000000021 CT001 1
这样的哇,这个方式就会报错,这样应该怎样判断呢