Declare @SQL VARCHAR (300), @ i int, @ n int, @ a int
Set @ a = 1
SET @ SQL = '123 | ABC | XXX | CCC'
Set @ n = len (@SQL) -len (Replace (@SQL, '|', '))
Set @ i = 1
While @a <= @ n
Begin
SELECT SUBSTRING (@ SQL, @ i, Patindex ('% |%', @ SQL) -1)
SELECT @ i = charIndex ('|', @ SQL, @ i 1) 1
Set @ a = @ a 1
end
// second
Declare @s nvarchar (1000)
Set @ s = '123 | ABC | XXX | CCC'
Set @ s = 'SELECT' '' Replace (@S, '|', '' AS S Union All SELECT '' ') "' ''
Print @S
EXEC (@S)
//
Declare @s nvarchar (1000)
Set @ s = '123 | ABC | XXX | CCC'
Set @ s = 'SELECT * INTO ## T from (select' ' replace (@S,' | ',' '' AS S Union All Select '') '' '' ') T Select * From ## t '
Print @S
EXEC (@S)
