HANA SQL function to split a comma delimited string into substrings

Question

I have the following HANA Procedure:

CREATE PROCEDURE SP_LIT()
AS

BEGIN
DECLARE count INT;
DECLARE pos INT;
DECLARE value NVARCHAR(100);

value := 'R,A';


IF LENGTH(:value) > 0 THEN

    value := :value + ',';
    pos   := LOCATE(:value,',',1);
END IF;
    WHILE :pos > 0 DO
    BEGIN
        INSERT INTO [O/P table] VALUES (LEFT(:value,:pos-1));
        value := RIGHT(:value, LENGTH (:value)-:pos);
        pos := LOCATE(:value,',',1);

    END;
    END WHILE;
END;

Everything seems fine but on execution the following error is thrown:

Error: invalid number exception: invalid number: not a valid number string 'R,A'

Any idea where am I going wrong?

I don't know much about HANA but it would help to know which line it ocurrs on. It might be that the target table's column is a number data type and you are trying to put a string into it. The functions you are using don't appear to requuire numbers. Normally in an INSERT statement you need to specify the columns being inserted into, but again, I don'y know much about HANA — Nick.Mc, Sep 12 '17 at 07:29
See earlier questions on SO, e.g. https://stackoverflow.com/questions/44110999/how-to-split-multiple-values-from-a-row-into-separate-rows — Christoph G, Sep 12 '17 at 13:47

score 0 · Answer 1 · answered Sep 12 '17 at 09:06

0

I found the solution. The '+' sign in concatenation should be replaced by '||' pipe operator.

answered Sep 12 '17 at 09:06

Muskaan

55
2
9

HANA SQL function to split a comma delimited string into substrings

1 Answers1