MS-SQL(시퀄) 서버에서 저장 프로시저(Stored Procedure; SP)를 사용하다 보면 흔히 접하는 문제들 중 하나이다. 표준 시퀄 쿼리는 배열을 지원하지 않기 때문에 여러가지 꼼수들을 사용해서 문자열을 배열로 변환시키곤 하는데, 아래 소개할 방법도 그런 것들 중 하나이다.
시퀄 서버 2005 버전부터 PATINDEX()1라는 함수를 새롭게 도입했다. 와일드카드(%)를 이용해서 문자열의 위치를 찾아내는 함수인데, 이를 이용하면 손쉽게 문자열을 잘라서 배열 형태의 테이블로 저장할 수 있게 된다. 아래 스크립트를 보자.
DECLARE @items NVARCHAR(MAX)
SET @items = '111,222,333,444,555,666,777,888,999'
DECLARE @delimiter NVARCHAR(1)
SET @delimiter = ','
DECLARE @item NVARCHAR(MAX)
SET @item = NULL
DECLARE @results TABLE (
Item NVARCHAR(MAX)
)
WHILE LEN(@items) > 0
BEGIN
DECLARE @index INT
SET @index = PATINDEX('%' + @delimiter + '%', @items)
IF @index > 0
BEGIN
SET @item = SUBSTRING(@items, 0, @index)
SET @items = SUBSTRING(@items, LEN(@item + @delimiter) + 1, LEN(@items))
INSERT INTO @results ( Item ) VALUES ( @item )
END
ELSE
BEGIN
SET @item = @items
SET @items = NULL
INSERT INTO @results ( Item ) VALUES ( @item )
END
END
SELECT * FROM @results1-2번 라인은 문자열을 지정하는 부분이다. 만약 저장 프로시저 혹은 펑션으로 만들고 싶다면 이부분은 파라미터로 받아들이면 된다.
4-5번 라인은 구분자를 지정하는 부분이다. 만약 저장 프로시저 혹은 펑션으로 만들고 싶다면 이부분은 파라미터로 받아들이면 된다.
10-12번 라인은 결과를 저장하는 테이블 변수이다. 펑션으로 만들고 싶다면 이부분을 리턴값으로 지정하면 된다.
12번 이후가 실제 내용이 될텐데, 간단하게 설명하자면 PATINDEX() 함수를 통해 구분자를 찾아서 해당 위치까지 문자열을 잘라낸 후 테이블에 값을 저장한다. 그렇게 다 저장을 하면 해당 테이블을 리턴해주면 끝. 만약 PATINDEX()와 같은 함수가 다른 DBMS에서도 존재한다면 위의 방법을 적용시킬 수도 있을 것이다.
참고로, PATINDEX() 함수 말고도 CHARINDEX()2라는 함수도 있는데, 이 둘의 차이는 와일드카드 문자(%)를 쓸 수 있는가(PATINDEX()) 없는가(CHARINDEX())가 전부이다.3
참조: