我在sql server的查询视窗下要做如下两个操作
1。将表tablename的所有记录的字段fieldname1(是5位整型值,如99502)值都相应减40000怎么写这个sql 语句?
2。将表tablename的所有记录的字段fieldname2(是字符串,如X99502)值都相应改为X后面的数字减去40000,(结果是X59502)怎么写这个sql 语句?
1。将表tablename的所有记录的字段fieldname1(是5位整型值,如99502)值都相应减40000怎么写这个sql 语句?
2。将表tablename的所有记录的字段fieldname2(是字符串,如X99502)值都相应改为X后面的数字减去40000,(结果是X59502)怎么写这个sql 语句?
select right(fieldname2,len(fieldname2)-1)-40000 from tablename
?
WHEN 5 THEN fieldname1 - 40000
ELSE fieldname1 END as fieldname1,
right(fieldname2,len(fieldname2)-1)-40000
FROM tablename
2.select fieldname2='X'+cast(replace(fieldname2,'X','')-40000 as int) from tablename
select fieldname1=fieldname1-40000 from tablename,
fieldname2='X'+cast(replace(fieldname2,'X','')-40000 as varchar) from tablename
select substring(fieldname2,1,1)+ cast(cast(substring(fieldname2,2,5)as int)-40000 as varchar(10))
fieldname2='X'+cast(replace(fieldname2,'X','')-40000 as varchar) from tablename
set fieldname1 = fieldname1 - 40000
set fieldname2 = left(fieldname2,1) + cast( (cast(right(fieldname2,4) as int) - 4000) as varchar)
fieldname2=left(fieldname2,1)+cast(stuff(fieldname2,1,1,'')-40000 as varchar)
set fieldname2 = left(fieldname2,1) + cast( (cast(right(fieldname2,4) as int) - 4000) as varchar)