구분자 분리(Split) 반환 함수
화면단에서 멀티 체크박스 선택 등으로 인해 여러 개의 값을 하나의 파라미터로 받아서 XML의 SQL WHERE 조건으로 넘기려 한다. 이때 SQL에서는 받은 여러 개의 값을 각각 분리해 하나씩 반환해주는 함수를 만들려고 한다. 예) WHERE KEY IN (‘A’, ‘B’, ‘C’, ‘D’)
‘A,B,C,D’ 이렇게 화면단에서 , 구분자를 이용해 여러개의 값을 하나의 변수로 받아 DB에 최종전달되면 DB에서는 , 구분자를 분리시켜 A, B , C, D 각각의 값을 반환한다.
1. 문자형으로 값 반환
함수 설명 : SplitStrings( ‘값’, ‘구분자’)
SELECT * FROM SplitStrings('A,B,C,D', ',')
구분자를 '-' 로 해서 반환
SELECT * FROM SplitStrings('A-B-C-D', '-')
사용예) in 조건
@AAA 테이블에는 아래의 값 등록됨
이중에서 WHERE 절의 IN 조건을 이용해 A, B, C만 조회
SELECT idx, id FROM @AAA
WHERE id in (
select * from SplitStrings('A,B,C',',')
)
위 SplitStrings() 함수 select 절은 아래와 동일한 의미
SELECT idx, id FROM @AAA
WHERE id in (
'A',
'B',
'C',
)
□ CREATE문
CREATE FUNCTION [dbo].[SplitStrings]
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
RETURN (
SELECT Item = CONVERT(varchar, Item)
FROM
(
SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
FROM
(
SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
) AS a
CROSS APPLY [XML].nodes('i') AS x(i)
) AS y
WHERE Item IS NOT NULL
);
2. 정수형으로 값 반환
@AAA테이블에는 아래와 같이 정수형 값들이 존재함
인수에 주어진 값을 각각 int형으로 변환하여 반환함
SELECT idx, id FROM @AAA
WHERE id in (
select * from SplitInts('1,2,3',',')
)
아래와 동일한 의미
SELECT idx, id FROM @AAA
WHERE id in (1,2, 3)
□ CREATE 생성문
CREATE FUNCTION [dbo].[SplitInts]
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
RETURN (
SELECT Item = CONVERT(int, Item)
FROM
(
SELECT Item = x.i.value('(./text())[1]', 'int')
FROM
(
SELECT [XML] = CONVERT(XML, '<i>'+ REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
) AS a
CROSS APPLY [XML].nodes('i') AS x(i)
) AS y
WHERE Item IS NOT NULL
);
'IT 공부 > SQL, DB' 카테고리의 다른 글
| [MS SQL] FOR XML PATH와 STUFF 활용 (0) | 2018.11.28 |
|---|---|
| [MS SQL] 과거 5개년 년도 보여주기 (다중테이블반환 함수, 임시테이블 활용) (0) | 2018.11.06 |
| [MS SQL] PRIMARY KEY 컬럼에 널체크시 INDEX 풀림(속도 느려짐) (0) | 2018.10.31 |
| [MySQL] 한글 문자 ???? 깨져서 저장되는 현상 (UTF8) (0) | 2018.10.31 |
