--> liangCK小梁 于2008-10-08 --> 生成测试数据: #T IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T CREATE TABLE #T (id VARCHAR(6),indentdetail VARCHAR(7)) INSERT INTO #T SELECT 'A10001','L+D+2G' UNION ALL SELECT 'A10002','L+2D+2G' UNION ALL SELECT 'A10003','L+2D+5G' UNION ALL SELECT 'A10004','2L+D+3F' UNION ALL SELECT 'A10005','L+D+4G'--SQL查询如下: GO CREATE FUNCTION dbo.fn_GetSum ( @indentdetail VARCHAR(7) ) RETURNS int AS BEGIN DECLARE @re int; DECLARE @tmp VARCHAR(5); DECLARE @c varchar(5); SET @re=0; SET @c=''; WHILE CHARINDEX('+',@indentdetail)>0 BEGIN SET @tmp=LEFT(@indentdetail,CHARINDEX('+',@indentdetail)-1);
IF PATINDEX('%[0-9]%',@tmp)<=0 SET @re=@re+1 ELSE BEGIN SET @re=@re+LEFT(@tmp,PATINDEX('%[^0-9]%',@tmp)-1) END
SET @indentdetail=STUFF(@indentdetail,1,CHARINDEX('+',@indentdetail),'') END
IF @indentdetail<>'' BEGIN SET @c=LEFT(@indentdetail,PATINDEX('%[^0-9]%',@indentdetail)-1) SET @re=@re+CAST(@c AS INT) END RETURN @re END GOSELECT id,dbo.fn_GetSum(indentdetail) AS indentdetail FROM #T GODROP FUNCTION dbo.fn_GetSum/* id indentdetail ------ ------------ A10001 4 A10002 5 A10003 8 A10004 6 A10005 6(5 行受影响)*/
--> liangCK小梁 于2008-10-08 --> 生成测试数据: #T IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T CREATE TABLE #T (id VARCHAR(6),indentdetail VARCHAR(7)) INSERT INTO #T SELECT 'A10001','L+D+2G' UNION ALL SELECT 'A10002','L+2D+2G' UNION ALL SELECT 'A10003','L+2D+5G' UNION ALL SELECT 'A10004','2L+D+3F' UNION ALL SELECT 'A10005','L+D+4G'--SQL查询如下:SELECT id , indentdetail=CASE WHEN PATINDEX('%[0-9]%',indentdetail1)<=0 THEN 1 ELSE LEFT(indentdetail1,PATINDEX('%[^0-9]%',indentdetail1)-1) END + CASE WHEN PATINDEX('%[0-9]%',indentdetail2)<=0 THEN 1 ELSE LEFT(indentdetail2,PATINDEX('%[^0-9]%',indentdetail2)-1) END + CASE WHEN PATINDEX('%[0-9]%',indentdetail3)<=0 THEN 1 ELSE LEFT(indentdetail3,PATINDEX('%[^0-9]%',indentdetail3)-1) END FROM ( SELECT id,CAST(PARSENAME(REPLACE(indentdetail,'+','.'),3) AS VARCHAR(5)) AS indentdetail1, CAST(PARSENAME(REPLACE(indentdetail,'+','.'),2) AS VARCHAR(5)) AS indentdetail2, CAST(PARSENAME(REPLACE(indentdetail,'+','.'),1) AS VARCHAR(5)) AS indentdetail3 FROM #T ) AS tGO /* id indentdetail ------ ------------ A10001 4 A10002 5 A10003 8 A10004 6 A10005 6(5 行受影响)*/
create function f_x(@exp varchar(20)) returns int as begin declare @i int set @i=0 declare @s varchar(20) while charindex('+',@exp)>0 begin set @s=substring(@exp,1,charindex('+',@exp)-1) set @exp=substring(@exp,charindex('+',@exp)+1,len(@exp)-charindex('+',@exp)) set @i=@i+(case when len(@s)=1 then 1 else cast(substring(@s,1,len(@s)-1) as int) end) end set @i=@i+case when len(@exp)=1 then 1 else cast(substring(@exp,1,len(@exp)-1) as int) end return(@i) end
那如果该表有两条数据是: id indentdetail A10001 L A10002 L+2D+2G+2K+2U你们上面写的东西还成立吗?
--> liangCK小梁 于2008-10-08 --> 生成测试数据: #T IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T CREATE TABLE #T (id VARCHAR(6),indentdetail VARCHAR(7)) INSERT INTO #T SELECT 'A10001','L' UNION ALL SELECT 'A10002','L+2D+2G'--SQL查询如下:GO CREATE FUNCTION dbo.fn_GetSum ( @indentdetail VARCHAR(7) ) RETURNS int AS BEGIN DECLARE @re int; DECLARE @tmp VARCHAR(5); DECLARE @c varchar(5); SET @re=0; SET @c='';
IF CHARINDEX('+',@indentdetail)=0 BEGIN SET @c=LEFT(@indentdetail,PATINDEX('%[^0-9]%',@indentdetail)-1) IF PATINDEX('%[0-9]%',@c)<=0 SET @re=@re+1 ELSE BEGIN SET @re=@re+LEFT(@c,PATINDEX('%[^0-9]%',@c)-1) END
RETURN @re END
WHILE CHARINDEX('+',@indentdetail)>0 BEGIN SET @tmp=LEFT(@indentdetail,CHARINDEX('+',@indentdetail)-1);
IF PATINDEX('%[0-9]%',@tmp)<=0 SET @re=@re+1 ELSE BEGIN SET @re=@re+LEFT(@tmp,PATINDEX('%[^0-9]%',@tmp)-1) END
SET @indentdetail=STUFF(@indentdetail,1,CHARINDEX('+',@indentdetail),'') END
IF @indentdetail<>'' BEGIN SET @c=LEFT(@indentdetail,PATINDEX('%[^0-9]%',@indentdetail)-1) SET @re=@re+CAST(@c AS INT) END RETURN @re END GOSELECT id,dbo.fn_GetSum(indentdetail) AS indentdetail FROM #T GODROP FUNCTION dbo.fn_GetSum /* id indentdetail ------ ------------ A10001 1 A10002 5(2 行受影响)*/
--> liangCK小梁 于2008-10-08 --> 生成测试数据: #T IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T CREATE TABLE #T (id VARCHAR(6),indentdetail VARCHAR(10)) INSERT INTO #T SELECT 'A10001','L+T1000' UNION ALL SELECT 'A10002','T1000+T500' UNION ALL SELECT 'A10001','L' UNION ALL SELECT 'A10002','L+2D+2G' --SQL查询如下:GO CREATE FUNCTION dbo.fn_GetSum ( @indentdetail VARCHAR(10) ) RETURNS int AS BEGIN DECLARE @re int; DECLARE @tmp VARCHAR(5); DECLARE @c varchar(5); SET @re=0; SET @c='';
IF CHARINDEX('+',@indentdetail)=0 BEGIN SET @c=LEFT(@indentdetail,PATINDEX('%[^0-9]%',@indentdetail)-1) IF PATINDEX('%[0-9]%',@c)<=0 SET @re=@re+1 ELSE BEGIN SET @re=@re+LEFT(@c,PATINDEX('%[^0-9]%',@c)-1) END
RETURN @re END
WHILE CHARINDEX('+',@indentdetail)>0 BEGIN SET @tmp=LEFT(@indentdetail,CHARINDEX('+',@indentdetail)-1);
SET @c=LEFT(@tmp,PATINDEX('%[^0-9]%',@tmp))
IF PATINDEX('%[0-9]%',@c)<=0 OR @c='' OR @c IS NULL SET @re=@re+1 ELSE BEGIN SET @re=@re+LEFT(@c,PATINDEX('%[^0-9]%',@c)-1) END
SET @indentdetail=STUFF(@indentdetail,1,CHARINDEX('+',@indentdetail),'') END
IF @indentdetail<>'' BEGIN
SET @c=LEFT(@indentdetail,PATINDEX('%[^0-9]%',@indentdetail)-1)
IF PATINDEX('%[0-9]%',@c)<=0 OR @c='' OR @c IS NULL SET @re=@re+1 ELSE SET @re=@re+CAST(@c AS INT) END RETURN @re END GOSELECT id,dbo.fn_GetSum(indentdetail) AS indentdetail FROM #T GODROP FUNCTION dbo.fn_GetSum /* id indentdetail ------ ------------ A10001 2 A10002 2 A10001 1 A10002 5(4 行受影响)*/
--> 测试数据: [s] if object_id('[s]') is not null drop table [s] create table [s] (id varchar(6),indentdetail varchar(11)) insert into [s] select 'A10001','L+T1000' union all select 'A10002','T1000+T500' union all select 'A10002','T1000+2T500' union all select 'A10002','T1000' union all select 'A10004','L+D+2G' union all select 'A10005','L+2D+2G' union all select 'A10006','L+2D+5G' union all select 'A10007','2L+D+3F' union all select 'A10008','L+D+4G' go--创建函数 create function fs(@val varchar(100)) returns int as begin declare @n int set @n=0 if (@val is null or @val='') set @n = 0 else begin set @val=@val+'+' while(charindex('+',@val)>0) begin if(patindex('%[0-9]%',left(@val,1))=0) set @n=@n+1 else set @n=@n + cast(left(left(@val,charindex('+',@val)-1),patindex('%[^0-9]%',left(@val,charindex('+',@val)-1))-1) as int) set @val = substring(@val,charindex('+',@val)+1,len(@val)) end end return @n end go select id,indentdetail=dbo.fs(indentdetail) from [s] --结果: id indentdetail ------ ------------ A10001 2 A10002 2 A10002 3 A10002 1 A10004 4 A10005 5 A10006 8 A10007 6 A10008 6
--将函数更改一下,避免字段中全部是数字的情况,如:43,4+5等等 create function fs(@val varchar(100)) returns int as begin declare @n int set @n=0 if (@val is null or @val='') set @n = 0 else begin set @val=@val+'+' while(charindex('+',@val)>0) begin if(patindex('%[0-9]%',left(@val,1))=0) set @n=@n+1 else begin if(patindex('%[^0-9]%',left(@val,charindex('+',@val)-1))=0) set @n=@n + cast(left(@val,charindex('+',@val)-1) as int) else set @n=@n + cast(left(left(@val,charindex('+',@val)-1),patindex('%[^0-9]%',left(@val,charindex('+',@val)-1))-1) as int) end set @val = substring(@val,charindex('+',@val)+1,len(@val)) end end return @n end go--> 测试数据: [s] if object_id('[s]') is not null drop table [s] create table [s] (id varchar(6),indentdetail varchar(11)) insert into [s] select 'A10001','L+T1000' union all select 'A10002','T1000+T500' union all select 'A10002','T1000+2T500' union all select 'A10002','T1000' union all select 'A10004','L+D+2G' union all select 'A10005','L+2D+2G' union all select 'A10006','L+2D+5G' union all select 'A10007','2L+D+3F' union all select 'A10008','L+D+4G' union all select 'A10008','5+4' union all select 'A10008','54'select id,indentdetail=dbo.fs(indentdetail) from s
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id VARCHAR(6),indentdetail VARCHAR(7))
INSERT INTO #T
SELECT 'A10001','L+D+2G' UNION ALL
SELECT 'A10002','L+2D+2G' UNION ALL
SELECT 'A10003','L+2D+5G' UNION ALL
SELECT 'A10004','2L+D+3F' UNION ALL
SELECT 'A10005','L+D+4G'--SQL查询如下:
GO
CREATE FUNCTION dbo.fn_GetSum
(
@indentdetail VARCHAR(7)
)
RETURNS int
AS
BEGIN
DECLARE @re int;
DECLARE @tmp VARCHAR(5);
DECLARE @c varchar(5);
SET @re=0;
SET @c='';
WHILE CHARINDEX('+',@indentdetail)>0
BEGIN
SET @tmp=LEFT(@indentdetail,CHARINDEX('+',@indentdetail)-1);
IF PATINDEX('%[0-9]%',@tmp)<=0
SET @re=@re+1
ELSE
BEGIN
SET @re=@re+LEFT(@tmp,PATINDEX('%[^0-9]%',@tmp)-1)
END
SET @indentdetail=STUFF(@indentdetail,1,CHARINDEX('+',@indentdetail),'')
END
IF @indentdetail<>''
BEGIN
SET @c=LEFT(@indentdetail,PATINDEX('%[^0-9]%',@indentdetail)-1)
SET @re=@re+CAST(@c AS INT)
END
RETURN @re
END
GOSELECT id,dbo.fn_GetSum(indentdetail) AS indentdetail
FROM #T GODROP FUNCTION dbo.fn_GetSum/*
id indentdetail
------ ------------
A10001 4
A10002 5
A10003 8
A10004 6
A10005 6(5 行受影响)*/
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id VARCHAR(6),indentdetail VARCHAR(7))
INSERT INTO #T
SELECT 'A10001','L+D+2G' UNION ALL
SELECT 'A10002','L+2D+2G' UNION ALL
SELECT 'A10003','L+2D+5G' UNION ALL
SELECT 'A10004','2L+D+3F' UNION ALL
SELECT 'A10005','L+D+4G'--SQL查询如下:SELECT id ,
indentdetail=CASE
WHEN PATINDEX('%[0-9]%',indentdetail1)<=0
THEN 1
ELSE LEFT(indentdetail1,PATINDEX('%[^0-9]%',indentdetail1)-1)
END +
CASE
WHEN PATINDEX('%[0-9]%',indentdetail2)<=0
THEN 1
ELSE LEFT(indentdetail2,PATINDEX('%[^0-9]%',indentdetail2)-1)
END +
CASE
WHEN PATINDEX('%[0-9]%',indentdetail3)<=0
THEN 1
ELSE LEFT(indentdetail3,PATINDEX('%[^0-9]%',indentdetail3)-1)
END
FROM
(
SELECT id,CAST(PARSENAME(REPLACE(indentdetail,'+','.'),3) AS VARCHAR(5)) AS indentdetail1,
CAST(PARSENAME(REPLACE(indentdetail,'+','.'),2) AS VARCHAR(5)) AS indentdetail2,
CAST(PARSENAME(REPLACE(indentdetail,'+','.'),1) AS VARCHAR(5)) AS indentdetail3
FROM #T
) AS tGO
/*
id indentdetail
------ ------------
A10001 4
A10002 5
A10003 8
A10004 6
A10005 6(5 行受影响)*/
returns int
as
begin
declare @i int
set @i=0
declare @s varchar(20)
while charindex('+',@exp)>0
begin
set @s=substring(@exp,1,charindex('+',@exp)-1)
set @exp=substring(@exp,charindex('+',@exp)+1,len(@exp)-charindex('+',@exp))
set @i=@i+(case when len(@s)=1 then 1 else cast(substring(@s,1,len(@s)-1) as int) end)
end
set @i=@i+case when len(@exp)=1 then 1 else cast(substring(@exp,1,len(@exp)-1) as int) end
return(@i)
end
id indentdetail
A10001 L
A10002 L+2D+2G+2K+2U你们上面写的东西还成立吗?
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id VARCHAR(6),indentdetail VARCHAR(7))
INSERT INTO #T
SELECT 'A10001','L' UNION ALL
SELECT 'A10002','L+2D+2G'--SQL查询如下:GO
CREATE FUNCTION dbo.fn_GetSum
(
@indentdetail VARCHAR(7)
)
RETURNS int
AS
BEGIN
DECLARE @re int;
DECLARE @tmp VARCHAR(5);
DECLARE @c varchar(5);
SET @re=0;
SET @c='';
IF CHARINDEX('+',@indentdetail)=0
BEGIN
SET @c=LEFT(@indentdetail,PATINDEX('%[^0-9]%',@indentdetail)-1)
IF PATINDEX('%[0-9]%',@c)<=0
SET @re=@re+1
ELSE
BEGIN
SET @re=@re+LEFT(@c,PATINDEX('%[^0-9]%',@c)-1)
END
RETURN @re
END
WHILE CHARINDEX('+',@indentdetail)>0
BEGIN
SET @tmp=LEFT(@indentdetail,CHARINDEX('+',@indentdetail)-1);
IF PATINDEX('%[0-9]%',@tmp)<=0
SET @re=@re+1
ELSE
BEGIN
SET @re=@re+LEFT(@tmp,PATINDEX('%[^0-9]%',@tmp)-1)
END
SET @indentdetail=STUFF(@indentdetail,1,CHARINDEX('+',@indentdetail),'')
END
IF @indentdetail<>''
BEGIN
SET @c=LEFT(@indentdetail,PATINDEX('%[^0-9]%',@indentdetail)-1)
SET @re=@re+CAST(@c AS INT)
END
RETURN @re
END
GOSELECT id,dbo.fn_GetSum(indentdetail) AS indentdetail
FROM #T GODROP FUNCTION dbo.fn_GetSum
/*
id indentdetail
------ ------------
A10001 1
A10002 5(2 行受影响)*/
id indentdetail
A10001 L+T1000
A10002 T1000+T500
A10002 T1000+2T500
A10002 T1000
这样呢?
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id VARCHAR(6),indentdetail VARCHAR(10))
INSERT INTO #T
SELECT 'A10001','L+T1000' UNION ALL
SELECT 'A10002','T1000+T500' UNION ALL
SELECT 'A10001','L' UNION ALL
SELECT 'A10002','L+2D+2G'
--SQL查询如下:GO
CREATE FUNCTION dbo.fn_GetSum
(
@indentdetail VARCHAR(10)
)
RETURNS int
AS
BEGIN
DECLARE @re int;
DECLARE @tmp VARCHAR(5);
DECLARE @c varchar(5);
SET @re=0;
SET @c='';
IF CHARINDEX('+',@indentdetail)=0
BEGIN
SET @c=LEFT(@indentdetail,PATINDEX('%[^0-9]%',@indentdetail)-1)
IF PATINDEX('%[0-9]%',@c)<=0
SET @re=@re+1
ELSE
BEGIN
SET @re=@re+LEFT(@c,PATINDEX('%[^0-9]%',@c)-1)
END
RETURN @re
END
WHILE CHARINDEX('+',@indentdetail)>0
BEGIN
SET @tmp=LEFT(@indentdetail,CHARINDEX('+',@indentdetail)-1);
SET @c=LEFT(@tmp,PATINDEX('%[^0-9]%',@tmp))
IF PATINDEX('%[0-9]%',@c)<=0 OR @c='' OR @c IS NULL
SET @re=@re+1
ELSE
BEGIN
SET @re=@re+LEFT(@c,PATINDEX('%[^0-9]%',@c)-1)
END
SET @indentdetail=STUFF(@indentdetail,1,CHARINDEX('+',@indentdetail),'')
END
IF @indentdetail<>''
BEGIN
SET @c=LEFT(@indentdetail,PATINDEX('%[^0-9]%',@indentdetail)-1)
IF PATINDEX('%[0-9]%',@c)<=0 OR @c='' OR @c IS NULL
SET @re=@re+1
ELSE
SET @re=@re+CAST(@c AS INT)
END
RETURN @re
END
GOSELECT id,dbo.fn_GetSum(indentdetail) AS indentdetail
FROM #T GODROP FUNCTION dbo.fn_GetSum
/*
id indentdetail
------ ------------
A10001 2
A10002 2
A10001 1
A10002 5(4 行受影响)*/
if object_id('[s]') is not null drop table [s]
create table [s] (id varchar(6),indentdetail varchar(11))
insert into [s]
select 'A10001','L+T1000' union all
select 'A10002','T1000+T500' union all
select 'A10002','T1000+2T500' union all
select 'A10002','T1000' union all
select 'A10004','L+D+2G' union all
select 'A10005','L+2D+2G' union all
select 'A10006','L+2D+5G' union all
select 'A10007','2L+D+3F' union all
select 'A10008','L+D+4G'
go--创建函数
create function fs(@val varchar(100))
returns int
as
begin
declare @n int
set @n=0
if (@val is null or @val='')
set @n = 0
else
begin
set @val=@val+'+'
while(charindex('+',@val)>0)
begin
if(patindex('%[0-9]%',left(@val,1))=0)
set @n=@n+1
else
set @n=@n + cast(left(left(@val,charindex('+',@val)-1),patindex('%[^0-9]%',left(@val,charindex('+',@val)-1))-1) as int)
set @val = substring(@val,charindex('+',@val)+1,len(@val))
end
end
return @n
end
go
select id,indentdetail=dbo.fs(indentdetail) from [s]
--结果:
id indentdetail
------ ------------
A10001 2
A10002 2
A10002 3
A10002 1
A10004 4
A10005 5
A10006 8
A10007 6
A10008 6
--将函数更改一下,避免字段中全部是数字的情况,如:43,4+5等等
create function fs(@val varchar(100))
returns int
as
begin
declare @n int
set @n=0
if (@val is null or @val='')
set @n = 0
else
begin
set @val=@val+'+'
while(charindex('+',@val)>0)
begin
if(patindex('%[0-9]%',left(@val,1))=0)
set @n=@n+1
else
begin
if(patindex('%[^0-9]%',left(@val,charindex('+',@val)-1))=0)
set @n=@n + cast(left(@val,charindex('+',@val)-1) as int)
else
set @n=@n + cast(left(left(@val,charindex('+',@val)-1),patindex('%[^0-9]%',left(@val,charindex('+',@val)-1))-1) as int)
end
set @val = substring(@val,charindex('+',@val)+1,len(@val))
end
end
return @n
end
go--> 测试数据: [s]
if object_id('[s]') is not null drop table [s]
create table [s] (id varchar(6),indentdetail varchar(11))
insert into [s]
select 'A10001','L+T1000' union all
select 'A10002','T1000+T500' union all
select 'A10002','T1000+2T500' union all
select 'A10002','T1000' union all
select 'A10004','L+D+2G' union all
select 'A10005','L+2D+2G' union all
select 'A10006','L+2D+5G' union all
select 'A10007','2L+D+3F' union all
select 'A10008','L+D+4G' union all
select 'A10008','5+4' union all
select 'A10008','54'select id,indentdetail=dbo.fs(indentdetail) from s