sqlserver 刪除表中 指定字元串

源表T

"單據編號"              　"航班計劃日期"        "航班號"          "起飛航站代碼"          "降落航站代碼"
"C-026413700"      "2013-2-11"           "CA1231"        "PEK"                        "XIY"
"C-026413699"      "2013-2-11"           "CA1231"        "PEK"                        "XIY"
"C-026413698"      "2013-2-11"           "CA1231"        "PEK"                        "XIY"
"C-026413697"      "2013-2-11"           "CA1231"        "PEK"                        "XIY"
"C-026413696"      "2013-2-11"           "CA1231"        "PEK"                        "XIY"

目標表T：

單據編號                     航班計劃日期           航班號              起飛航站代碼             降落航站代碼
C-026413700            2013-2-11            CA1231              PEK                           XIY
C-026413699            2013-2-11            CA1231              PEK                           XIY
C-026413698            2013-2-11            CA1231              PEK                           XIY
C-026413697            2013-2-11            CA1231              PEK                           XIY
C-026413696            2013-2-11            CA1231              PEK                           XIY

SQL語句：

 1 --如果資料庫中存在 表T，則刪除表T
 2 if not object_id(N'T') is null
 3     drop table T
 4 Go
 5 --新建表T
 6 Create table T(["單據編號"] nvarchar(33),["航班計劃日期"] nvarchar(31),["航班號"] nvarchar(28),["起飛航站代碼"] nvarchar(25),["降落航站代碼"] nvarchar(25))
 7 SELECT * FROM dbo.T
 8 --新增數據
 9 Insert T
10 select N'"C-026413700"',N'"2013-2-11"',N'"CA1231"',N'"PEK"',N'"XIY"' union all
11 select N'"C-026413699"',N'"2013-2-11"',N'"CA1231"',N'"PEK"',N'"XIY"' union all
12 select N'"C-026413698"',N'"2013-2-11"',N'"CA1231"',N'"PEK"',N'"XIY"' union all
13 select N'"C-026413697"',N'"2013-2-11"',N'"CA1231"',N'"PEK"',N'"XIY"' union all
14 select N'"C-026413696"',N'"2013-2-11"',N'"CA1231"',N'"PEK"',N'"XIY"'
15 Go
16 SELECT * FROM dbo.T
17 --更新表T中數據：將數據中 雙隱號 刪除
18 UPDATE  T
19 SET     ["單據編號"] = REPLACE(["單據編號"], '"', '') ,
20         ["航班計劃日期"] = REPLACE(["航班計劃日期"], '"', ''),
21         ["航班號"] = REPLACE(["航班號"], '"', ''),
22         ["起飛航站代碼"] = REPLACE(["起飛航站代碼"], '"', ''),
23         ["降落航站代碼"] = REPLACE(["降落航站代碼"], '"', '')
24 SELECT * FROM dbo.T
25 --更新表T中頭欄位：將頭欄位中 雙隱號 刪除
26 EXEC sp_rename 'T.["單據編號"]','單據編號'
27 EXEC sp_rename 'T.["航班計劃日期"]','航班計劃日期'
28 EXEC sp_rename 'T.["航班號"]','航班號'
29 EXEC sp_rename 'T.["起飛航站代碼"]','起飛航站代碼'
30 EXEC sp_rename 'T.["降落航站代碼"]','降落航站代碼'
31 SELECT * FROM dbo.T
32 --刪除表T中數據
33 delete from T
34 SELECT * FROM dbo.T

執行結果：

sqlserver》單擊資料庫》新建查詢(N)》複製SQL語句到空白處》 ！執行(X)

​