sql如何把字符串中分割符号以外的数值乘以任意倍数
我现在只能写出如果如果数值是个位的是没有问题，如果是10位的或者百位的就不行了，求高手写出一个不管个位数还是10位数或者百位数的都能翻倍。
下面是把 PB1的值翻倍成PB
select pb=ltrim(rtrim(str(convert(int,substring(pb,1,1))*2)))
+'|'+ ltrim(rtrim(str(CONVERT(int,SUBSTRING(pb,3,1)*2))))
+'|'+ ltrim(rtrim(str(CONVERT(int,SUBSTRING(pb,5,1)*2))))
+'|'+ ltrim(rtrim(str(CONVERT(int,SUBSTRING(pb,7,1)*2))))
,pb as pb1 from yw_dh2xiang where dhcode = '1028'
输出结果
pb pb1
6|8|10|12 3|4|5|6
8|8|10|6 4|4|5|6
12|10|10|12 6|5|5|6
SQL? UPDATE
[解决办法]
create table liq
(pb1 varchar(30))
insert into liq
select '3
[解决办法]
4
[解决办法]
5
[解决办法]
6' union all
select '4
[解决办法]
4
[解决办法]
5
[解决办法]
6' union all
select '6
[解决办法]
5
[解决办法]
5
[解决办法]
6' union all
select '650
[解决办法]
51
[解决办法]
6' union all
select '3
[解决办法]
53
[解决办法]
122' union all
select '32
[解决办法]
35
[解决办法]
67' union all
select '100
[解决办法]
300
[解决办法]
200'
create function dbo.fnpb1
(@x varchar(30))
returns varchar(30)
as
begin
declare @r varchar(30),@t varchar(30),@i int
select @x=@x+'
[解决办法]
',@r='',@t='',@i=1
while(@i<=len(@x))
begin
if substring(@x,@i,1)='
[解决办法]
'
begin
select @r=@r+rtrim(cast(@t as int)*2)+'
[解决办法]
'
select @t=''
end
if substring(@x,@i,1)<>'
[解决办法]
'
begin
select @t=@t+substring(@x,@i,1)
end
select @i=@i+1
end
return left(@r,len(@r)-1)
end
select pb1,
dbo.fnpb1(pb1) 'pb2'
from liq
/*
pb1 pb2
------------------------------ ------------------------------
3
[解决办法]
4
[解决办法]
5
[解决办法]
6 6
[解决办法]
8
[解决办法]
10
[解决办法]
12
4
[解决办法]
4
[解决办法]
5
[解决办法]
6 8
[解决办法]
8
[解决办法]
10
[解决办法]
12
6
[解决办法]
5
[解决办法]
5
[解决办法]
6 12
[解决办法]
10
[解决办法]
10
[解决办法]
12
650
[解决办法]
51
[解决办法]
6 1300
[解决办法]
102
[解决办法]
12
3
[解决办法]
53
[解决办法]
122 6
[解决办法]
106
[解决办法]
244
32
[解决办法]
35
[解决办法]
67 64
[解决办法]
70
[解决办法]
134
100
[解决办法]
300
[解决办法]
200 200
[解决办法]
600
[解决办法]
400
(7 row(s) affected)
*/
CREATE TABLE t1
(
id INT,
col VARCHAR(50)
)
INSERT INTO t1
select 1,'3
[解决办法]
4
[解决办法]
51
[解决办法]
6' UNION ALL
select 2,'4
[解决办法]
12
[解决办法]
5
[解决办法]
6' UNION ALL
select 3,'11
[解决办法]
5
[解决办法]
105
[解决办法]
6'
SELECT * FROM t1
--定义倍数
DECLARE @a INT=3
;WITH aaa AS
(
SELECT*
FROMt1 AS a WITH(NOLOCK) INNER JOIN
master..spt_values AS b WITH(NOLOCK) ON b.number<=DATALENGTH(a.col)+1
ANDSUBSTRING('
[解决办法]
'+a.col,b.number,1)='
[解决办法]
'
ANDb.[type]='P'
)
,bbb AS
(
SELECT*,
SUBSTRING(col,number,CHARINDEX('
[解决办法]
',col+'
[解决办法]
',number)-number) AS col1
FROMaaa
)
,ccc AS
(
SELECTid,
CAST(col1 AS INT)*@a AS col2
FROMbbb
)
SELECTid,
STUFF((SELECT '
[解决办法]
'+LTRIM(col2) FROM ccc WHERE id=a.id FOR XML PATH('')),1,1,'') AS col
FROMccc AS a
GROUP BY id
