Create Function SQL

Question

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

What's your db2 version and platform?

Mark Barinstein
– Mark Barinstein

2023-01-31 05:23:54 +00:00
Commented Jan 31, 2023 at 5:23 — Mark Barinstein
– Mark Barinstein, Commented Jan 31, 2023 at 5:23
Mark Barinstein, DB2 v11.1.4040.491

Jhon
– Jhon

2023-01-31 08:06:50 +00:00
Commented Jan 31, 2023 at 8:06 — Jhon
– Jhon, Commented Jan 31, 2023 at 8:06

Mark Barinstein · Accepted Answer · 2023-01-31 12:09:02Z

1

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

edited Jan 31, 2023 at 12:09

answered Jan 31, 2023 at 10:59

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jhon Over a year ago

Thanks, but I need to create this function.

Mark Barinstein Over a year ago

A table function is used in the FROM clause, not in the SELECT list. Moreover, your function has a single return column, but your result set has 2 ones. So, do you want a table function with 2 result set columns doin aggregation inside?

Jhon Over a year ago

Yeah, I need to table function set columns doing aggregation inside

Mark Barinstein Over a year ago

Look at the updated answer.

Jhon Over a year ago

Thanks)), why Db2 is so difficult?)))

|

Collectives™ on Stack Overflow

Create Function SQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related