我现在只能写出如果如果数值是个位的是没有问题,如果是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
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
--实现split功能 的函数
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
endgo
参考google split()函数用法
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)
*/
(
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 *
FROM t1 AS a WITH(NOLOCK) INNER JOIN
master..spt_values AS b WITH(NOLOCK) ON b.number<=DATALENGTH(a.col)+1
AND SUBSTRING('|'+a.col,b.number,1)='|'
AND b.[type]='P'
)
,bbb AS
(
SELECT *,
SUBSTRING(col,number,CHARINDEX('|',col+'|',number)-number) AS col1
FROM aaa
)
,ccc AS
(
SELECT id,
CAST(col1 AS INT)*@a AS col2
FROM bbb
)
SELECT id,
STUFF((SELECT '|'+LTRIM(col2) FROM ccc WHERE id=a.id FOR XML PATH('')),1,1,'') AS col
FROM ccc AS a
GROUP BY id原来:
id col
1 3|4|51|6
2 4|12|5|6
3 11|5|105|6现在:
id col
1 9|12|153|18
2 12|36|15|18
3 33|15|315|18