SQL 一列的每一行加值

[gagaxiaoyuer]

SELECT code FROM TABLEA
结果：CODE
COM50
COM88
COM106
COM73
COM27
COM33
COM23
我要怎么样在每列得到的结果加上2
想要结果：CODE
COM52
COM90
COM108
COM75
。。。

[wzy_love_sly]

[Quote=引用 4 楼 gagaxiaoyuer 的回复:]
两克星星了！！！
[/Quote]

呵呵

[gagaxiaoyuer]

可以直接从查询语句里面改吗？

[SQLnewlearner]

try
SELECT code+cast(2 as varcahr) FROM TABLEA

[utpcb]

就COM太好办了

if object_id('tb') is not null
drop table tb
go
create table tb(code varchar(50))
insert into tb select 'COM50'
insert into tb select 'COM88'
insert into tb select 'COM106'
insert into tb select 'COM73'
insert into tb select 'COM27'
insert into tb select 'COM33'
insert into tb select 'COM23'

update tb set code='COM'+ltrim(cast(replace(code,'com','') as int)+2)

select * from tb

[gagaxiaoyuer]

可以直接从查询语句里面改吗？

[SQLnewlearner]

不好意思上面弄错了弄成后面+2这个字符了。。

[wzy_love_sly]

if object_id('tb') is not null
drop table tb
go
create table tb(code varchar(50))
insert into tb select 'COM50'
insert into tb select 'COM88'
insert into tb select 'COM106'
insert into tb select 'COM73'
insert into tb select 'COM27'
insert into tb select 'COM33'
insert into tb select 'COM23'


select 'COM'+ltrim(cast(replace(code,'com','') as int)+2) as code from tb

COM52
COM90
COM108
COM75
COM29
COM35
COM25

会修改不会查询

[wenwt7]

SELECT
code=
case when lift(code,3)=com then 'com'+((cast(substring(code,4)) as int)+2)
end
FROM TABLEA

[gagaxiaoyuer]

select distinct p2.FLOCAL_NAME,fconcentrator_no,fdevice_code
from power_device,PARTNER P1,PARTNER P2,PARTNER P3,PARTNER P4
where p1.fupline=power_device.fconcentrator_no
and fdevice_code like '%COM%'
AND p1.fupline=p2.fpartner_code
AND p2.fupline=p3.fpartner_code
AND p3.fupline=p4.fpartner_code


是这句帮我改写fdevice_code这列加上2

[rhq12345]

select left(code ,patindex('%[0-9]%',code )-1)+
convert(varchar(8000),(convert(int,substring(code ,patindex('%[0-9]%',code ),8000))+2))
 FROM TABLEA 

或者

select left(code ,3)+convert(varchar(8000),convert(int,right(code ,len(code )-3))+2)
 FROM TABLEA 

[wzy_love_sly]

select distinct p2.FLOCAL_NAME,fconcentrator_no,
'COM'+ltrim(cast(replace(fdevice_code,'com','') as int)+2) as fdevice_code
from power_device,PARTNER P1,PARTNER P2,PARTNER P3,PARTNER P4
where p1.fupline=power_device.fconcentrator_no
and fdevice_code like '%COM%'
AND p1.fupline=p2.fpartner_code
AND p2.fupline=p3.fpartner_code
AND p3.fupline=p4.fpartner_code 

[-狙击手-]

[code=BatchFile][/code]

[gagaxiaoyuer]

多谢大鸟 多谢大家

[gagaxiaoyuer]

要不要我帮你加多个星星 哈哈

[lgxyz]

SELECT 'COM'+LTRIM(CONVERT(INT,(REPLACE(CODE,'COM','')))+2) AS CODE FROM TB

[窝抓了个羊]

update table
set code = 'COM'+convert(char(3),substring(code,4,len(code))+2)

[wzy_love_sly]

[Quote=引用 20 楼 gagaxiaoyuer 的回复:]
要不要我帮你加多个星星 哈哈
[/Quote]

呵呵，早呢，ok我也散了。。..

[lff642]

如果格式都是数字在后面的话.可以做.要是不固定的话.那就不好做.

[lb6201883]

if object_id('test.dbo.tb') is not null
drop table test.dbo.tb
create table tb(code varchar(100))
insert tb
select 'com50'
union all
select 'com88'
union all
select 'com106'
union all
select 'com73'
union all
select 'com27'
union all
select 'com33'
union all
select 'com23'

select * from tb

select left(code,3)+cast(cast(substring(code,4,len(code)) as int)+2 as varchar(10)) from tb

[SQLnewlearner]

declare a cursor for
select code from tb for update of code
open a
declare @code varchar(100),@i int, @b int
fetch next from a into @code
while @@fetch_status=0
begin
select @b=(select len(code)-3 )from tb where code=@code
set @i=cast(right(@code,@b) as int)+2
update tb set code=left(code,3)+cast(@i as varchar) from tb where current of a
fetch next from a into @code
end
close a
deallocate a