콤마로 된 문자열을 테이블로 변환 함수

콤마로 된 문자열을 테이블로 변환 함수

 

[ DelimitedSplit8K ]

 

1. 테이블 반환 함수

2. ItemNumber : 순번, Item : 각 콤마구분값

3. InputParameter : @pString - 문자열, @pDelimiter - 구분값(char(1))

1. CREATE FUNCTION dbo.DelimitedSplit8K  
2. /***************************************************************************************************  
3.  Purpose:  
4.  Split a given string at a given delimiter and return a list of the split elements (items).  
5.   
6.  Usage Example:  
7.  SELECT *  
8.    FROM dbo.DelimitedSplit8K(@StringToSplit, @Delimiter)  
9.   
10.  Returns:  
11.  iTVF containing the following:  
12.  ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)  
13.  Item       = Element value as a VARCHAR(8000)  
14.   
15.  Notes:  
16.  1. Optimized for VARCHAR(8000) or less.  
17.  2. Optimized for single character delimiter.  
18.  3. Optimized for use with CROSS APPLY.  
19.  4. Does not "trim" elements just in case leading or trailing blanks are intended.  
20.  5. cteTally concept originally by Iztek Ben Gan and "decimalized" by Lynn Pettis (and others) for a  
21.     bit of extra speed and finally redacted by Jeff Moden for a different slant on readability and   
22.     compactness.   
23.  6. If you don't know how a Tally table can be used to replace loops, please see the following...  
24.     http://www.sqlservercentral.com/articles/T-SQL/62867/  
25.   
26.  Revision History:  
27.  Rev 00 - 20 Jan 2010 - Jeff Moden   
28.         - Base 10 redaction for CTE.  
29.  Rev 01 - 08 Mar 2010 - Jeff Moden  
30.         - Changed UNION ALL to UNPIVOT for bit of extra speed.  
31.  Rev 02 - 13 Mar 2010 - Jeff Moden  
32.         - Removed one additional concatenation and one subtraction from the SUBSTRING in the   
33.           SELECT List.  
34. ***************************************************************************************************/  
35. --===== Define I/O parameters  
36.         (  
37.         @pString    VARCHAR(8000),  
38.         @pDelimiter CHAR(1)  
39.         )  
40. RETURNS TABLE  
41. AS  
42. RETURN  
43. WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000).  
44.       E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL   
45.                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL   
46.                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),--10  
47.       E2(N) AS (SELECT 1 FROM E1 a, E1 b),   --100  
48.       E4(N) AS (SELECT 1 FROM E2 a, E2 b),   --10,000  
49. cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4)  
50. --===== Do the split  
51.  SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,  
52.         SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item  
53.    FROM cteTally  
54.   WHERE N < LEN(@pString) + 2  
55.     AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter  
56. ;  

 

<사용예>

 

1. 문자열 관련

SELECT * FROM DelimitedSplit8K('을지문덕,홍길동,가가가,일반적,소리샘,케이티,왔구나,뭘까요,납니다요,ㅋㅋㅋㅋㅋㅋㅋㅋ,긴문장을보여드리겠습니다. 뭐가 길까요알아서 맞춰보시길',',')  

 

 

2. 테이블과 조인

 

  - CROSS APPLY를 위해 SQL2005 이상 가능 ( 호환성모드 90 이상 )

1. SELECT t.아이디,t.콤마로된필드,b.itemNumber,b.item  
2. FROM 대상테이블 t  
3. CROSS APPLY dbo.DelimitedSplit8K(t.m_interest,',') B  
4. WHERE isnull(t.콤마로된필드,'') <> ''  
5. ORDER BY T.아이디  

 

 

 

[ fn_Split ]

 

1. 테이블 반환 함수

2. 각 콤마구분값

3. InputParameter : @Sql - 문자열, @Splits - 구분값

1. CREATE FUNCTION [dbo]. [fn_Split] ( @Sql VARCHAR (8000), @Splits VARCHAR (10))    
2.     RETURNS @temp TABLE (item VARCHAR (100))    
3. AS    
4. BEGIN   
5.     Declare @i Int    
6.     SET @Sql    =  RTrim   (LTrim (@Sql))    
7.     SET @i      =  CharIndex   (@Splits, @Sql)    
8.     WHILE @i  >  =1    
9.     Begin    
10.         INSERT @temp VALUES(Left (@Sql, @i-1))    
11.         SET @Sql    =  SUBSTRING   (@Sql, @i + 1, LEN (@Sql ) - @i)    
12.         SET @i      =  CHARINDEX   (@Splits, @Sql)    
13.     END ;    
14.   
15.   
16.     IF @Sql     < >  ''    
17.         INSERT @temp VALUES(@Sql)    
18.    
19.     RETURN  
20. END    

 

<사용예>

1. SELECT * FROM fn_Split('을지문덕,홍길동,가가가,일반적,소리샘,케이티,왔구나,뭘까요,납니다요,ㅋㅋㅋㅋㅋㅋㅋㅋ,긴문장을보여드리겠습니다. 뭐가 길까요알아서 맞춰보시길',',')  

 

CREATE FUNCTION dbo.DelimitedSplit8K /*************************************************************************************************** Purpose: Split a given string at a given delimiter and return a list of the split elements (items). Usage Example: SELECT * FROM dbo.DelimitedSplit8K(@StringToSplit, @Delimiter) Returns: iTVF containing the following: ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST) Item = Element value as a VARCHAR(8000) Notes: 1. Optimized for VARCHAR(8000) or less. 2. Optimized for single character delimiter. 3. Optimized for use with CROSS APPLY. 4. Does not "trim" elements just in case leading or trailing blanks are intended. 5. cteTally concept originally by Iztek Ben Gan and "decimalized" by Lynn Pettis (and others) for a bit of extra speed and finally redacted by Jeff Moden for a different slant on readability and compactness. 6. If you don't know how a Tally table can be used to replace loops, please see the following... http://www.sqlservercentral.com/articles/T-SQL/62867/ Revision History: Rev 00 - 20 Jan 2010 - Jeff Moden - Base 10 redaction for CTE. Rev 01 - 08 Mar 2010 - Jeff Moden - Changed UNION ALL to UNPIVOT for bit of extra speed. Rev 02 - 13 Mar 2010 - Jeff Moden - Removed one additional concatenation and one subtraction from the SUBSTRING in the SELECT List. ***************************************************************************************************/ --===== Define I/O parameters ( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE AS RETURN WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000). 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),--10 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000 cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4) --===== Do the split SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber, SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item FROM cteTally WHERE N < LEN(@pString) + 2 AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter ; SELECT * FROM DelimitedSplit8K('을지문덕,홍길동,가가가,일반적,소리샘,케이티,왔구나,뭘까요,납니다요,ㅋㅋㅋㅋㅋㅋㅋㅋ,긴문장을보여드리겠습니다. 뭐가 길까요알아서 맞춰보시길',',') SELECT t.아이디,t.콤마로된필드,b.itemNumber,b.item FROM 대상테이블 t CROSS APPLY dbo.DelimitedSplit8K(t.m_interest,',') B WHERE isnull(t.콤마로된필드,'') <> '' ORDER BY T.아이디 CREATE FUNCTION [dbo]. [fn_Split] ( @Sql VARCHAR (8000), @Splits VARCHAR (10)) RETURNS @temp TABLE (item VARCHAR (100)) AS BEGIN Declare @i Int SET @Sql = RTrim (LTrim (@Sql)) SET @i = CharIndex (@Splits, @Sql) WHILE @i > =1 Begin INSERT @temp VALUES(Left (@Sql, @i-1)) SET @Sql = SUBSTRING (@Sql, @i + 1, LEN (@Sql ) - @i) SET @i = CHARINDEX (@Splits, @Sql) END ; IF @Sql < > '' INSERT @temp VALUES(@Sql) RETURN END SELECT * FROM fn_Split('을지문덕,홍길동,가가가,일반적,소리샘,케이티,왔구나,뭘까요,납니다요,ㅋㅋㅋㅋㅋㅋㅋㅋ,긴문장을보여드리겠습니다. 뭐가 길까요알아서 맞춰보시길',',')