先拆分再判断--各种字符串分函数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
update tb set ok=(case when charindex('1',rtrim(PARSENAME(replace(字段,',','.')),5))>0 and charindex('7',rtrim(PARSENAME(replace(字段,',','.')),4))>0 and charindex('5',rtrim(PARSENAME(replace(字段,',','.')),3))>0 and charindex('2',rtrim(PARSENAME(replace(字段,',','.')),2))>0 and charindex('8',rtrim(PARSENAME(replace(字段,',','.')),1))>0 then 'true' else 'false' end)
update tb set ok=(case when charindex('1',rtrim(PARSENAME(replace(字段,',','.'),5)))>0 and charindex('7',rtrim(PARSENAME(replace(字段,',','.'),4)))>0 and charindex('5',rtrim(PARSENAME(replace(字段,',','.'),3)))>0 and charindex('2',rtrim(PARSENAME(replace(字段,',','.'),2)))>0 and charindex('8',rtrim(PARSENAME(replace(字段,',','.'),1)))>0 then 'true' else 'false' end)
PARSENAME 分割的的字符串不能大于4段。所以无效
应该怎么写SQL语句呢,请大家指点
declare @str varchar(50) set @str='12,85963,561,4563,68523' ;with cte as( select idd=row_number()over(order by getdate()),number,col=@str from master..spt_values where type='p' and substring(@str+',',number,1)=',' ) select OK=case when sum( case when (idd=1 and charindex('1',groupStr)>0) or (idd=2 and charindex('7',groupStr)>0) or (idd=3 and charindex('5',groupStr)>0) or (idd=4 and charindex('2',groupStr)>0) or (idd=5 and charindex('8',groupStr)>0) then 1 else 0 end )=5 then 'true' else 'false' end from ( select a.idd,groupStr=substring(@str+',',charindex(',',','+@str,isnull(b.number,0)),a.number-isnull(b.number,0)-1) from cte a left join cte b on a.idd=b.idd+1 )t/* OK --------- false */ ;with cte as( select idd=row_number()over(order by getdate()),number,col=@str from master..spt_values where type='p' and substring(@str+',',number,1)=',' ) select OK=case when sum( case when (idd=1 and charindex('1',groupStr)>0) or (idd=2 and charindex('8',groupStr)>0) or (idd=3 and charindex('5',groupStr)>0) or (idd=4 and charindex('4',groupStr)>0) or (idd=5 and charindex('6',groupStr)>0) then 1 else 0 end )=5 then 'true' else 'false' end from ( select a.idd,groupStr=substring(@str+',',charindex(',',','+@str,isnull(b.number,0)),a.number-isnull(b.number,0)-1) from cte a left join cte b on a.idd=b.idd+1 )t/* OK --------- true */
按你这个例子可以简单写成这样: declare @strValue nvarchar(30) declare @charSplit char declare @Sp1 int,@Sp2 int,@Sp3 int,@Sp4 int select @strValue='12,85963,561,4563,68523' select @charSplit=',' --get split chars' postion select @Sp1=CHARINDEX(@charSplit,@strValue,0) select @Sp2=CHARINDEX(@charSplit,@strValue,@Sp1+1) select @Sp3=CHARINDEX(@charSplit,@strValue,@Sp2+1) select @Sp4=CHARINDEX(@charSplit,@strValue,@Sp3+1) --return result select case when CHARINDEX('1',SUBSTRING(@strValue,0,@Sp1),0)>0 and CHARINDEX('7',SUBSTRING(@strValue,@Sp1+1,@Sp2-@Sp1-1),0)>0 and CHARINDEX('5',SUBSTRING(@strValue,@Sp2+1,@Sp3-@Sp2-1),0)>0 and CHARINDEX('2',SUBSTRING(@strValue,@Sp3+1,@Sp4-@Sp3-1),0)>0 and CHARINDEX('8',SUBSTRING(@strValue,@Sp4+1,len(@strValue)-@Sp4),0)>0 then 1 else 0 end 成批数据的处理就根据数据特征改成存储过程来用
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
when charindex('1',rtrim(PARSENAME(replace(字段,',','.')),5))>0
and charindex('7',rtrim(PARSENAME(replace(字段,',','.')),4))>0
and charindex('5',rtrim(PARSENAME(replace(字段,',','.')),3))>0
and charindex('2',rtrim(PARSENAME(replace(字段,',','.')),2))>0
and charindex('8',rtrim(PARSENAME(replace(字段,',','.')),1))>0
then 'true' else 'false' end)
when charindex('1',rtrim(PARSENAME(replace(字段,',','.'),5)))>0
and charindex('7',rtrim(PARSENAME(replace(字段,',','.'),4)))>0
and charindex('5',rtrim(PARSENAME(replace(字段,',','.'),3)))>0
and charindex('2',rtrim(PARSENAME(replace(字段,',','.'),2)))>0
and charindex('8',rtrim(PARSENAME(replace(字段,',','.'),1)))>0
then 'true' else 'false' end)
set @str='12,85963,561,4563,68523'
;with cte as(
select idd=row_number()over(order by getdate()),number,col=@str
from master..spt_values
where type='p' and substring(@str+',',number,1)=','
)
select OK=case when sum( case when (idd=1 and charindex('1',groupStr)>0)
or (idd=2 and charindex('7',groupStr)>0)
or (idd=3 and charindex('5',groupStr)>0)
or (idd=4 and charindex('2',groupStr)>0)
or (idd=5 and charindex('8',groupStr)>0) then 1 else 0 end )=5
then 'true' else 'false' end
from (
select a.idd,groupStr=substring(@str+',',charindex(',',','+@str,isnull(b.number,0)),a.number-isnull(b.number,0)-1)
from cte a left join cte b on a.idd=b.idd+1
)t/*
OK
---------
false
*/
;with cte as(
select idd=row_number()over(order by getdate()),number,col=@str
from master..spt_values
where type='p' and substring(@str+',',number,1)=','
)
select OK=case when sum( case when (idd=1 and charindex('1',groupStr)>0)
or (idd=2 and charindex('8',groupStr)>0)
or (idd=3 and charindex('5',groupStr)>0)
or (idd=4 and charindex('4',groupStr)>0)
or (idd=5 and charindex('6',groupStr)>0) then 1 else 0 end )=5
then 'true' else 'false' end
from (
select a.idd,groupStr=substring(@str+',',charindex(',',','+@str,isnull(b.number,0)),a.number-isnull(b.number,0)-1)
from cte a left join cte b on a.idd=b.idd+1
)t/*
OK
---------
true
*/
declare @charSplit char
declare @Sp1 int,@Sp2 int,@Sp3 int,@Sp4 int
select @strValue='12,85963,561,4563,68523'
select @charSplit=','
--get split chars' postion
select @Sp1=CHARINDEX(@charSplit,@strValue,0)
select @Sp2=CHARINDEX(@charSplit,@strValue,@Sp1+1)
select @Sp3=CHARINDEX(@charSplit,@strValue,@Sp2+1)
select @Sp4=CHARINDEX(@charSplit,@strValue,@Sp3+1)
--return result
select case when CHARINDEX('1',SUBSTRING(@strValue,0,@Sp1),0)>0
and CHARINDEX('7',SUBSTRING(@strValue,@Sp1+1,@Sp2-@Sp1-1),0)>0
and CHARINDEX('5',SUBSTRING(@strValue,@Sp2+1,@Sp3-@Sp2-1),0)>0
and CHARINDEX('2',SUBSTRING(@strValue,@Sp3+1,@Sp4-@Sp3-1),0)>0
and CHARINDEX('8',SUBSTRING(@strValue,@Sp4+1,len(@strValue)-@Sp4),0)>0
then 1 else 0 end 成批数据的处理就根据数据特征改成存储过程来用