http://i.imgur.com/t91Mo3W.jpg
create table #a
(address char(30))
GO
insert #a
VALUES ('01X02Y03Z'),
('2341X666Y806Z')
GO
SELECT ADDRESS,
SUBSTRING ( ADDRESS,1,CHARINDEX('X',ADDRESS) ),
SUBSTRING ( ADDRESS,CHARINDEX('X',ADDRESS)+1,CHARINDEX('Y',ADDRESS) -
CHARINDEX('X',ADDRESS)),
SUBSTRING ( ADDRESS,CHARINDEX('Y',ADDRESS)+1,CHARINDEX('Z',ADDRESS) -
CHARINDEX('Y',ADDRESS)),
SUBSTRING ( ADDRESS,CHARINDEX('Y',ADDRESS)+1,CHARINDEX('Z',ADDRESS) -
CHARINDEX('Y',ADDRESS))+
SUBSTRING ( ADDRESS,CHARINDEX('X',ADDRESS)+1,CHARINDEX('Y',ADDRESS) -
CHARINDEX('X',ADDRESS))+
SUBSTRING ( ADDRESS,1,CHARINDEX('X',ADDRESS) )
FROM #A
※ 引述《SangoDragon (SANGO)》之銘言：
: 資料庫名稱： MS SQL
: 資料庫版本： SQL 2008
: 住址的值要重新排序
: 例如: Table: Employee
: 欄位: Address 改成 Address1
: