CODEWITHSUNDEEP
sql
Jhon
Jhon

Reputation: 3

Create Function SQL

I need to create a function that would split string by element. 'a1,a2,a1,a3' and result should be:

Value Counter
a1 2
a2 1
a3 1

This is example of my code (but it needs to work)

CREATE OR REPLACE FUNCTION SPLIT_STRING (
  IN_STRING VARCHAR(1000),
  IN_DELIM VARCHAR(20))
RETURNS TABLE (CNT INT)
  LANGUAGE SQL
  READS SQL DATA
  BEGIN
  DECLARE I INT DEFAULT 0;
  DECLARE CNT INT DEFAULT 0;
  WHILE I < LENGTH(IN_STRING)
  DO
    IF SUBSTR(IN_STRING, I, 1) = IN_DELIM THEN SET CNT = CNT + 1;
    END IF;
    SET I = I + 1;
  END WHILE;
  RETURN CNT;
END;

select SPLIT_STRING('a1,a2,a1,a3', ',') from SYSIBM.SYSDUMMY1

Upvotes: 0

Views: 127

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12339

There is no need to create such a function in Db2 for LUW.
You may use a built-in functionality.

select x.tok as value, count (1) as counter
from 
  -- Here is your table reference
  (values  'a1,a2,a1,a3') mytab (str)
, xmltable
(
  'for $id in tokenize($s, ",") return <i>{string($id)}</i>' 
  passing 
    mytab.str as "s"
  columns 
    tok varchar(4000) path '.'
) x
group by x.tok
VALUE COUNTER
a1 2
a2 1
a3 1

Update

The same with a table function:

CREATE OR REPLACE FUNCTION SPLIT_STRING 
(
  IN_STRING VARCHAR(1000)
, IN_DELIM VARCHAR(20)
)
RETURNS TABLE (TOKEN VARCHAR (100), CNT INT)
LANGUAGE SQL
READS SQL DATA
RETURN
select x.tok as value, count (1) as counter
from xmltable
(
  'for $id in tokenize($s, $p) return <i>{string($id)}</i>' 
  passing 
    IN_STRING as "s"
  , IN_DELIM as "p"
  columns 
    tok varchar (1000) path '.'
) x
group by x.tok


select *
from table (SPLIT_STRING ('a1,a2,a1,a3', ','))
TOKEN CNT
a1 2
a2 1
a3 1

fiddle

Upvotes: 1

Related Questions