Select Split một chuỗi thành bảng

SELECT * FROM Split('foo,bar,widget', ',')

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER FUNCTION [dbo].[Split]

(

   @String NVARCHAR(4000),

   @Delimiter NCHAR(1)

)

RETURNS TABLE

AS

RETURN

(

   WITH Split(stpos,endpos)

   AS(

       SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos

       UNION ALL

       SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)

           FROM Split

           WHERE endpos > 0

   )

   SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),

       'Cityname' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)

   FROM Split

)