CODEWITHSUNDEEP
sqldb2
Adarsh H D Dev
Adarsh H D Dev

Reputation: 618

How to create a SPLIT_STRING function in Db2

I am trying to write a db2 function, below is the code

CREATE OR REPLACE FUNCTION SPLIT_STRING (INPUT VARCHAR(8000), DELIMITER 
CHAR(1) DEFAULT ',')
RETURNS TABLE (str VARCHAR(8000))
SPECIFIC SPLIT_STRING
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
 DECLARE @MSG VARCHAR(30)
 SET @MSG ='INVALID INPUT'
 IF (INPUT IS NULL) THEN RETURN @MSG
ELSE
 RETURN
 WITH R1 (str, REMAINDER) AS
 (SELECT SUBSTR(INPUT, 1, LOCATE(DELIMITER, INPUT) -1) AS str,
    SUBSTR(INPUT, LOCATE(DELIMITER, INPUT) +1 , LENGTH(INPUT)) 
 REMAINDER FROM SYSIBM.SYSDUMMY1

UNION ALL
SELECT SUBSTR(REMAINDER, 1, LOCATE(DELIMITER, REMAINDER)-1) AS str,
   SUBSTR(REMAINDER, LOCATE(DELIMITER, REMAINDER)+1, LENGTH(REMAINDER)) 
REMAINDER  FROM R1
WHERE LOCATE(DELIMITER, REMAINDER) > 0

UNION ALL
SELECT SUBSTR(REMAINDER, LOCATE(DELIMITER, REMAINDER)+1, 
LENGTH(REMAINDER)) AS str,'' AS REMAINDER  FROM R1 WHERE REMAINDER <> ''
AND LOCATE(DELIMITER, REMAINDER) = 0

)SELECT str FROM R1
END IF
END;

If I remove the IF condition which am using for Input parameter validation function gets created fine else getting below error.

DB21034E  The command was processed as an SQL statement because it was 
not a valid Command Line Processor command.  During SQL processing it returned:SQL0104N  An unexpected token "SET" was found following "RE @MSG VARCHAR(30) ".  Expected tokens may include:  <SQL_variable_declarations>".  LINE NUMBER=8.  SQLSTATE=42601

Upvotes: 0

Views: 746

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

String splitter.

CREATE OR REPLACE FUNCTION SPLIT_STRING (INPUT VARCHAR(8000), DELIMITER CHAR(1) DEFAULT ',')
RETURNS TABLE (str VARCHAR(8000))
SPECIFIC SPLIT_STRING
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
return
select str
from xmltable('for $id in tokenize($s, $p) return <i>{string($id)}</i>' 
passing 
  --INPUT as "s"
  COALESCE(INPUT, CAST(RAISE_ERROR('70001', 'INVALID INPUT') AS VARCHAR(8000))) as "s"
, DELIMITER as "p"
columns 
  str varchar(8000) path '.'
) t;

Upvotes: 2

Related Questions