Add SQL Server statements to favorites

If object_id ('tb') is not null
Drop table TB
Go
Create Table Tb (name varchar (25 ))
Insert into TB
Select 'z, Y, W, C' Union all
Select '1, 2, 3, 4'

Select name = substring (A. Name, B. Number, (charindex (',', A. Name + ',', B. Number)-B. Number)
From tb a, Master... spt_values B where B. type = 'p' and B. number between 1 and Len (A. Name)
And substring (',' + A. Name, B. Number, 1) = ','

 

Select (charindex (',', 'z, Y, W, C' + ',', 2)-2
Select substring ('z, Y, W, C', 3, (charindex (',', 'z, Y, W, C' + ',', 3 )) -3)
Select substring (',' + 'Z, Y, W, C', 2, 1)

 

Select * From Tab

-- SQL2000 auxiliary table:
If object_id ('tempdb .. # num') is not null
Drop table # num
Go
Select Top 100 id = identity (INT,) into # num from syscolumns A, syscolumns B
Select
A. col1, col2 = substring (A. col2, B. ID, charindex (',', A. col2 + ',', B. ID)-B. ID)
From
Tab A, # num B
Where
Charindex (',' +. col2, B. ID) = B. id -- substring (',' +. col2, B. ID, 1) = ','

 

 

-- 2000 No auxiliary table is used
Select
A. col1, col2 = substring (A. col2, B. Number, charindex (',', A. col2 + ',', B. Number)-B. Number)
From
Tab A join Master... spt_values B
On B. type = 'p' and B. number between 1 and Len (A. col2)
Where
Substring (',' + A. col2, B. Number, 1) = ','

 

 

 

 

 

Select Col = substring (A. col2, B. Number ,)

 

 

 

 

 

 

 

 

Sql2005 uses XML:

Select
A. col1, B. col2
From
(Select col1, col2 = convert (XML, '<root> <v>' + Replace (col2, '</V> <v> ') + '</V> </root>') from tab)
Outer apply
(Select col2 = C. V. Value ('.', 'nvarchar (100) ') from A. col2.nodes ('/root/V') C (V) B

Select '<root> <v>' + Replace (name, '</V> <v> ') + '</V> </root>' from TB

 

 

Sql05 use CTE: -- this method Roy blog

; With Roy
(Select col1, col2 = cast (left (col2, charindex (',', col2 + ',')-1) as nvarchar (100 )), split = cast (stuff (col2 + ',', 1, charindex (',', col2 + ','), '') as nvarchar (100) from Tab
Union all
Select col1, col2 = cast (left (split, charindex (',', split)-1) as nvarchar (100), split = cast (stuff (split, 1, charindex (',', split), '') as nvarchar (100) from Roy where split>''
)
Select col1, col2 from Roy order by col1 option (maxrecursion 0)

Result:
/*
Col1 col2
----------------
1
1 B
1 c
2 D
2 E
3 F
*/

 

 

Original address: http://zywsqlserver100.blog.163.com/blog/static/116385209201003011151710? Fromdm & fromsearch & isfromsearchengine = Yes