CREATE VIEW VA AS
select A,
left(A,charindex('/',A)-1) AS B,
Left(stuff(A,1,charindex('/',A),''),charindex('/',stuff(A,1,charindex('/',A),''))-1) AS C,
Reverse(left(reverse(A),charindex('/',reverse(A))-1)) AS D
FROM TABLE1
GO
INSERT INTO TA VALUES('0/5/9')
INSERT INTO TA VALUES('01/15/12')
GO
INSERT TEST SELECT '0/5/9',NULL,NULL,NULL
INSERT TEST SELECT '01/15/12',NULL,NULL,NULL
SELECT * FROM TEST
UPDATE TEST SET
B=LEFT(A,CHARINDEX('/',A)-1),
C=SUBSTRING(A,CHARINDEX('/',A)+1,CHARINDEX('/',A,CHARINDEX('/',A))-1),
D=RIGHT(A,CHARINDEX('/',REVERSE(A))-1)
CREATE TABLE A(id INT,country VARCHAR(100))
INSERT A
SELECT 1,'中国;日本;韩国' UNION ALL
SELECT 2,'美国;意大利;法国' UNION ALL
SELECT 3,'德国'
SELECT * FROM A
-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT
A.ID,
COUNTRY = SUBSTRING(A.COUNTRY, B.ID, CHARINDEX(';', A.COUNTRY + ';', B.ID) - B.ID)
FROM A, # B
WHERE SUBSTRING(';' + a.COUNTRY, B.id, 1) = ';'
ORDER BY 1,2
GO