declare @s varchar(100) set @s=('select '+replace('1$2$3','$','+')) exec (@s)
分个字符不规则,写个函数 创建处理函数: create table A( a1 varchar(10), a2 varchar(10), a3 varchar(10) ) insert into a values('B3','C1','D\4') insert into a values('B31d','C12','D1\4') insert into a values('B31ds2','C13','D2\4') go create function dbo.f_str(@a varchar(10)) returns int as begin declare @cnt as int set @cnt = 0 declare @i as int declare @j as int declare @k1 as int declare @k2 as int set @i = 1 set @j = len(@a) set @k1 = 0 set @k2 = 0 while @i <= @j begin if substring(@a , @i , 1) between '0' and '9' begin if @k1 = 0 set @k1 = @i if @i = @j begin set @k2 = @j set @cnt = @cnt + cast(substring(@a , @k1 , (@k2 - @k1 + 1)) as int) end end else begin if @k1 > 0 begin set @k2 = @i - 1 set @cnt = @cnt + cast(substring(@a , @k1 , (@k2 - @k1 + 1)) as int) end set @k1 = 0 set @k2 = 0 end set @i = @i + 1 end return @cnt end go --调用函数 select * , [sum] = dbo.f_str(a1) + dbo.f_str(a2) + dbo.f_str(a3) from a drop function dbo.f_str drop table a /* a1 a2 a3 sum B3 C1 D\4 8 B31d C12 D1\4 48 B31ds2 C13 D2\4 52 */--以上为大乌龟代码
select SUM(CONVERT(int, REPLACE(Csum,'$','+'))) from A 这样写但报这个错: 消息 245,级别 16,状态 1,第 1 行 在将 varchar 值 '10+18' 转换成数据类型 int 时失败。求解。
给你个函数用用,只保留数字:CREATE FUNCTION [dbo].[ReturnNumeric] ( @Str VARCHAR(20) ) RETURNS INT AS BEGIN DECLARE @I INT=1
WHILE @I<=LEN(@Str) BEGIN IF SUBSTRING(@Str,@I,1) LIKE ('[^0-9]') BEGIN SET @Str=REPLACE(@Str,SUBSTRING(@Str,@I,1),'') --RETURN @Str END ELSE BEGIN SET @I=@I+1 END
END RETURN @Str END
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba') BEGIN DROP TABLE tba END GO CREATE TABLE tba ( Csum VARCHAR(100) ) GO INSERT INTO tba SELECT '1$2$3' UNION SELECT '23' UNION SELECT '23$34$34'GO IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'csum') BEGIN DROP FUNCTION csum END GO CREATE FUNCTION csum(@Str VARCHAR(100)) RETURNS INT AS BEGIN DECLARE @Sum INT DECLARE @Num INT DECLARE @Door_Int INT SET @Door_Int = 1 SET @Sum = 0WHILE @Door_Int > 0 BEGIN SET @Door_Int = CHARINDEX('$',@Str) IF @Door_Int = 0 BEGIN SET @Sum = @Sum + CAST(@Str AS INT) BREAK END SET @Num = CAST(LEFT(@Str,@Door_Int - 1) AS INT) SET @Str = RIGHT(@Str,LEN(@Str) - @Door_Int) SET @Sum = @Sum + @Num ENDRETURN @Sum ENDGOSELECT dbo.csum(csum) FROM tbasum 6 23 91
set @s=('select '+replace('1$2$3','$','+'))
exec (@s)
创建处理函数:
create table A(
a1 varchar(10),
a2 varchar(10),
a3 varchar(10)
)
insert into a values('B3','C1','D\4')
insert into a values('B31d','C12','D1\4')
insert into a values('B31ds2','C13','D2\4')
go
create function dbo.f_str(@a varchar(10))
returns int
as
begin
declare @cnt as int
set @cnt = 0
declare @i as int
declare @j as int
declare @k1 as int
declare @k2 as int
set @i = 1
set @j = len(@a)
set @k1 = 0
set @k2 = 0
while @i <= @j
begin
if substring(@a , @i , 1) between '0' and '9'
begin
if @k1 = 0
set @k1 = @i
if @i = @j
begin
set @k2 = @j
set @cnt = @cnt + cast(substring(@a , @k1 , (@k2 - @k1 + 1)) as int)
end
end
else
begin
if @k1 > 0
begin
set @k2 = @i - 1
set @cnt = @cnt + cast(substring(@a , @k1 , (@k2 - @k1 + 1)) as int)
end
set @k1 = 0
set @k2 = 0 end
set @i = @i + 1
end
return @cnt
end
go
--调用函数
select * , [sum] = dbo.f_str(a1) + dbo.f_str(a2) + dbo.f_str(a3)
from a drop function dbo.f_str drop table a
/* a1 a2 a3 sum
B3 C1 D\4 8
B31d C12 D1\4 48
B31ds2 C13 D2\4 52
*/--以上为大乌龟代码
消息 245,级别 16,状态 1,第 1 行
在将 varchar 值 '10+18' 转换成数据类型 int 时失败。求解。
你这个肯定会报错了!你去带后还是字符串,必须exec(@str)才可以。
还有这个问题需要写一个处理函数,直接处理也不行
(
@Str VARCHAR(20)
)
RETURNS INT
AS
BEGIN
DECLARE @I INT=1
WHILE @I<=LEN(@Str)
BEGIN
IF SUBSTRING(@Str,@I,1) LIKE ('[^0-9]')
BEGIN
SET @Str=REPLACE(@Str,SUBSTRING(@Str,@I,1),'')
--RETURN @Str
END
ELSE
BEGIN
SET @I=@I+1
END
END RETURN @Str
END
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
Csum VARCHAR(100)
)
GO
INSERT INTO tba
SELECT '1$2$3' UNION
SELECT '23' UNION
SELECT '23$34$34'GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'csum')
BEGIN
DROP FUNCTION csum
END
GO
CREATE FUNCTION csum(@Str VARCHAR(100))
RETURNS INT
AS
BEGIN
DECLARE @Sum INT
DECLARE @Num INT
DECLARE @Door_Int INT
SET @Door_Int = 1
SET @Sum = 0WHILE @Door_Int > 0
BEGIN
SET @Door_Int = CHARINDEX('$',@Str)
IF @Door_Int = 0
BEGIN
SET @Sum = @Sum + CAST(@Str AS INT)
BREAK
END
SET @Num = CAST(LEFT(@Str,@Door_Int - 1) AS INT)
SET @Str = RIGHT(@Str,LEN(@Str) - @Door_Int)
SET @Sum = @Sum + @Num
ENDRETURN @Sum
ENDGOSELECT dbo.csum(csum)
FROM tbasum
6
23
91