입력 문자열을 특정 문자로 나누어서 결과셋으로 리턴해주는 프로시저입니다.
다음의 표준 UDF들을 선언해줘야 실행할 수 있습니다.
ASCII_CHAR
LTRIM
RTRIM
STRLEN
SUBSTR
출처 : http://www.fbtalk.net/ViewTopic.aspx?id=154
SET TERM ## ;
CREATE OR ALTER PROCEDURE SPLITTEXT
(
ipTEXT VARCHAR(32000),
ipSPLITCHARS VARCHAR(20),
ipIGNORECHARS VARCHAR(20)
)
RETURNS
(
opTEXT VARCHAR(500))
AS
DECLARE VARIABLE vCurrChar VARCHAR(1);
DECLARE VARIABLE vIDX INTEGER;
DECLARE VARIABLE vLen INTEGER;
BEGIN
-- if the text is null/blank then exit
vLen = STRLEN(ipTEXT);
IF (ipTEXT IS NULL OR ipTEXT = '') THEN
BEGIN
SUSPEND;
EXIT;
END
-- default to spaces and line sep if no split char defined
IF ((ipSPLITCHARS IS NULL) OR (ipSPLITCHARS = '')) THEN
ipSPLITCHARS = ' ' || ASCII_CHAR(13) || ASCII_CHAR(10);
IF (ipIGNORECHARS IS NULL) THEN
ipIGNORECHARS = '';
/* were starting with the first character and
on the first octet */
vIDX = 1;
opTEXT = '';
-- get the 1st char from the string
vCurrChar = CAST(SUBSTR(ipTEXT, :vIDX, :vIDX) AS VARCHAR(1));
WHILE (vIDX <= vLen) DO
BEGIN
-- is it a seperator char?
IF (ipSPLITCHARS CONTAINING vCURRCHAR) THEN
BEGIN
opTEXT = RTRIM(LTRIM(opTEXT));
-- return the current text, if there is any
IF (opTEXT <> '') THEN
SUSPEND;
-- reset the output buffer
opTEXT = '';
END ELSE
BEGIN
IF ((vCurrChar <> '') AND (ipIGNORECHARS NOT CONTAINING vCurrChar)) THEN
opTEXT = opTEXT || vCurrChar;
END
-- increment the char index
vIDX = vIDX + 1;
-- get the Nth char from the string
vCurrChar = CAST(SUBSTR(ipTEXT, :vIDX, :vIDX) AS VARCHAR(1));
END
-- if there is any text left then return it
IF (opTEXT <> '') THEN
SUSPEND;
END ##
SET TERM ; ##
COMMIT;
|