T-Sql - Karakter parçalama


CREATE FUNCTION [dbo].[KARAKTERALMA]
        (@METIN VARCHAR(8000), @AYRAC CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                         
       E2(N) AS (SELECT 1 FROM E1 a, E1 b),
       E4(N) AS (SELECT 1 FROM E2 a, E2 b),
 cteTally(N) AS (
                 SELECT TOP (ISNULL(DATALENGTH(@METIN),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@METIN,t.N,1) = @AYRAC
                ),
cteLen(N1,L1) AS(
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@AYRAC,@METIN,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
 SELECT Sırano= ROW_NUMBER() OVER(ORDER BY l.N1),
        Acıklama= SUBSTRING(@METIN, l.N1, l.L1)
   FROM cteLen l
;


SELECT* FROM  [dbo].[KARAKTERALMA]('VEDAT,OZER,RAPOR',',')

Sırano Acıklama
1       VEDAT
2       OZER
3       RAPOR

Yorumlar