가끔 긴 문자열에서 각 단어들을 분리하여 테이블에 저장하고 싶을 경우가 있습니다. 이때 사용할 수 있는 쿼리를 소개하고자 합니다. 먼저 아래 쿼리를 실행합니다.

SET QUOTED_IDENTIFIER OFF
GO
DECLARE @str varchar(2000);
SELECT @str =
"A lonely moon about the sky,
A gentle flower in the breeze,
A giant cloud of smoky stars,
Some sticky honey made by bees.

The soft sweet voice of melody
And moist warm air, which we all breathe
Tremendous power of our minds
Which can make everyone believe..."

SELECT @str = REPLACE(@str, CHAR(10), ' ');
SELECT @str = REPLACE(@str, CHAR(13),  ' ' );

WITH ProcessText AS
(SELECT 1 as startPos, CHARINDEX (' ',@str, 1) as  spacePos 
UNION ALL
SELECT spacePos + 1, CHARINDEX (' ',@str, spacePos + 1)
FROM ProcessText WHERE spacePos > 0)
 
SELECT startPos,
        spacePos,
       SUBSTRING(@str, startPos, spacePos - startPos) AS singleWord
  INTO #words
  FROM ProcessText WHERE spacePos <> 0
 UNION ALL
 SELECT MAX(spacePos) + 1, 0, RIGHT(@str, LEN(@str) - MAX(spacePos))
   FROM ProcessText
 OPTION(MAXRECURSION 0);

위의 쿼리를 실행 후 #words 테이블을 조회해 보면 각 단어들이 모두 분리되어 각각의 위치 정보와 함께 저장되어 있음을 알 수 있습니다.

SELECT * FROM #words