Tsql column/column conversion-column-to-row splitting based on specific characters

(To csdn) Original: http://topic.csdn.net/u/20071026/16/59d85356-ff06-44e9-8e01-e6e1869a8a30.html

-- Form 1:
Create Table A (ID Int , Country Varchar ( 100 ))
Insert A
Select 1 , ' China; Japan; South Korea ' Union All
Select 2 , ' USA; Italy; France ' Union All
Select 3 , ' Germany '
Select * From A

--Create an auxiliary temporary table.
SelectTop8000ID=Identity(Int,1,1)
Into#FromSyscolumns 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

drop table , #
ID country
-- --------- ----------------
1 China; Japan; south Korea
2 USA; Italy; France
3 Germany

(The number of affected rows is3Rows)

Id country
-- ------------------
1 South Korea
1 Japan
1 China
2 France
2 USA
2 Italy
3 Germany

(The number of affected rows is7Rows)

Form 2:
Declare @ Table ( Varchar ( 20 ), B Varchar ( 20 ), C Varchar ( 20 ), D Varchar ( 20 ))
Insert @ Select ' A1 ' , ' B1 ' , ' C1 ' , ' D1/DA '
Union All Select ' A2 ' , ' B2 ' , ' C2 ' , ' D22/DA/da22 '
Union All Select ' A3 ' , ' B3 ' , ' C3 ' , ' D3 '

Declare@ TTable(IDIntIdentity(1,1), EInt)
Insert@ TSelectTop5001FromSyscolumns

Select A, B, c, Substring (D + ' / ' , ID, Charindex ( ' / ' , D + ' / ' , ID + 1 ) - ID) d
From @ A, @ T B
Where Substring ( ' / ' + D, ID, 1 ) = ' / '
-- Result
/* A B c d
---------------------------------------------------------------------------------
A1 B1 C1 d1
A2 B2 C2 D22
A3 B3 C3 D3
A1 B1 C1 da
A2 B2 C2 da
A2 B2 C2 da22