求SQL 语句

[灿烂阳光168]

在SQL SERVER表中有一列单号列，例如 ABC00001,ABC00002,ABC00003,ABC00004,ABC00005,ABC00006,ABC00007,ABC00008.
现在需要将第二个字符换成"D",即ADC00001,ADC00002,ADC00003,ADC00004,ADC00005,ADC00006,ADC00007,ADC00008.

求SQL 语句。

[黄_瓜]

substring()

[饮水需思源]

update 表 set 列=left(列,1)+'D'+right(列,6)

[feixianxxx]

replace (@s,'B','D')

[lihan6415151528]

CREATE TABLE TEST(NAME VARCHAR(20))
INSERT INTO TEST 
SELECT 'ABC00001' UNION ALL
SELECT 'ABC00002' UNION ALL 
SELECT 'ABC00003' UNION ALL
SELECT 'ABC00004' UNION ALL
SELECT 'ABC00005'

SELECT * FROM TEST

UPDATE TEST SET NAME=left(NAME,1)+'D'+right(NAME,6) 

SELECT * FROM TEST

NAME                 
-------------------- 
ADC00001
ADC00002
ADC00003
ADC00004
ADC00005

（所影响的行数为 5 行）

[feixianxxx]

update 表
set col=left(col,1)+'D'+right(col,len(col)-2) 

[百年树人]

update tb
set 单号=stuff(单号,2,1,'D')

[feixianxxx]

CREATE TABLE TESTk(col VARCHAR(20))
INSERT INTO TESTk
SELECT 'ABC00001' UNION ALL
SELECT 'ABC00002' UNION ALL 
SELECT 'ABC00003' UNION ALL
SELECT 'ABC00004' UNION ALL
SELECT 'ABC00005'
update TESTk
set col=left(col,1)+'D'+right(col,len(col)-2) 
select * from TESTk
/*
col
--------------------
ADC00001
ADC00002
ADC00003
ADC00004
ADC00005
*/

[水族杰纶]

-->Title:生成測試數據
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-27 14:45:59
 
if not object_id('tb') is null
	drop table tb
Go
Create table tb([Col1] nvarchar(8))
Insert tb
select N'ABC00001' union all
select N'ABC00002' union all
select N'ABC00003' union all
select N'ABC00004' union all
select N'ABC00005' union all
select N'ABC00006' union all
select N'ABC00007' union all
select N'ABC00008'
Go
update tb set col1=stuff(col1,2,1,'D')
select * from tb 
/*
Col1
--------
ADC00001
ADC00002
ADC00003
ADC00004
ADC00005
ADC00006
ADC00007
ADC00008

(8 個資料列受到影響)
*/

[华夏小卒]

update tb set 单号=left(单号,1) + 'D'+ substring(单号,3,len(单号)-2)

[feixianxxx]

[Quote=引用 6 楼 josy 的回复:]
SQL codeupdate tbset 单号=stuff(单号,2,1,'D')
[/Quote]
....忘记了 还能这么写。。。惭愧

[华夏小卒]

[Quote=引用 8 楼 wufeng4552 的回复:]
SQL code-->Title:生成測試數據
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-27 14:45:59ifnotobject_id('tb')isnulldroptable tbGoCreatetable tb([Col1]nvarchar(8))Insert tbselect N'ABC00001'unionallselect N'ABC0¡­
[/Quote]

水哥的好强悍啊

[liangCK]

简单问题复杂化:

DECLARE @str varchar(MAX);
SET @str = 'ABC00001,ABC00002,ABC00003,ABC00004,ABC00005,ABC00006,ABC00007,ABC00008.';

SELECT
    STUFF((SELECT CONVERT(xml,'<v>' 
               + REPLACE(@str,',','</v><v>')
                + '</v>') AS v).query('
         for $i in //v
            return concat(",",substring($i,1,1),"D",substring($i,3))
    ').value('.','varchar(MAX)'),1,1,'') AS v;

/*
v
--------------------------------
ADC00001 ,ADC00002 ,ADC00003 ,ADC00004 ,ADC00005 ,ADC00006 ,ADC00007 ,ADC00008.

(1 行受影响)
*/

[灿烂阳光168]

UPDATE tb
SET tb_no = SUBSTRING(tb_no, 1, 2) + 'D' + SUBSTRING(tb_no, 4, 7)
WHERE (tb_no LIKE '%ISO%')

出错：子查询返回的值多于一个

怎么回事？请指教