/*
Create function INSTR
iFind => Character you want to find
iStr => String
iOrder => if there are more than one character in string,
which you want to find
return location of that certain character
*/
DROP FUNCTION INSTR
GO
CREATE FUNCTION INSTR(iFind CHAR(1),iStr VARCHAR(1024),iOrder INTEGER)
RETURNS INTEGER
RETURN WITH N(COL,TXT,TXT1) AS (
SELECT 1 COL,TXT,SUBSTR(TXT,1,1)
FROM (SELECT iStr TXT
FROM SYSIBM.SYSDUMMY1) T
WHERE TXT > ''
UNION ALL
SELECT N.COL + 1,TXT,SUBSTR(TXT,N.COL+1,1)
FROM N
WHERE N.COL + 1 <= LENGTH(RTRIM(TXT))
)
SELECT COALESCE(COL,0) FROM
(
SELECT COL,TXT1,ROW_NUMBER() OVER() OD FROM N
WHERE TXT1 = iFind
) T
WHERE OD = iOrder;
-- test
SELECT INSTR(',','abcd,efghijkl,m',1) FROM SYSIBM.SYSDUMMY1;
==> Result
5
SELECT INSTR(',','abcd,efghijkl,m',2) FROM SYSIBM.SYSDUMMY1;
==> Result
14
