CODEWITHSUNDEEP
sqlhana
Peder Rice
Peder Rice

Reputation: 1794

HANA: Split string?

Is there a way to split a string in HANA?

Something similar to the equivalent in SQL Server: SELECT * FROM dbo.fnSplitString('valueA,valueB', ',')

Upvotes: 3

Views: 20177

Answers (5)

Charles P
Charles P

Reputation: 1

Solution using the SUBSTR_REGEXPR function. I've used this just last week in Hana as a Script_View and can confirm it works. 

Begin
VAR_OUT =
    select *
    from ( select
    ST.PARAM, -- Source table id
    NT.Element_Number, -- Occurrence number within source table row
SUBSTR_REGEXPR( '([;])([^;]*)(?=;)'
IN   CONCAT(CONCAT(';',ST.VALUE),';')
     OCCURRENCE NT.Element_Number
    GROUP 2
    ) splitted      -- string piece
from 
"_SYS_BIC"."Financial_Planning_and_Analysis/ZZTPARPAR_SOURCE" as ST, -- source  table
SERIES_GENERATE_INTEGER(1, 0, 10 ) as NT -- numbers table
    ) tbl
where splitted is not null
order by PARAM, Element_Number;
End

Before and After

Upvotes: 0

antonioplacerda
antonioplacerda

Reputation: 139

I know this question is a bit old, but I never found a good solution. So I created a Table UDF for this, and I think it's better than calling a procedure for this, since you don't need to be creating an addittional variable. Well, here is the code, hope it helps!

The credits go to Peder Rice, since I only adapted his code. Cheers!

    FUNCTION "TF_SPLIT_STRING" 
(
    i_string nvarchar(255)
) 
    RETURNS table(type char(1), value nvarchar(255))
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER AS
BEGIN
    --> DECLARATIONS
    -- The remaining string after checking for the delimiter
    declare v_remaining VARCHAR(256)    := :i_string;
    -- The current vale that is being handled
    declare v_current   VARCHAR(255)    := '';

    -- The current positions of the resulting arrays
    declare v_pos_include   INTEGER := 1;
    declare v_pos_exclude   INTEGER := 1;

    -- The arrays containing the results
    declare a_include   VARCHAR(12) array := ARRAY(0);
    declare a_exclude   VARCHAR(12) array := ARRAY(0);

    -- The delimiter and the excluder
    declare v_delimiter char(1) := ',';
    declare v_excluder char(1) := '-';

    --> SETUP BLOCK
    if substr(:v_remaining, length(:v_remaining), 1) <> :v_delimiter
    then
        v_remaining := :v_remaining || :v_delimiter;
    end if;

    --> EXECUTION BLOCK
    --  While there is a string to process
    --      ->  Split the string into the current value and the remaining
    --      ->  Check if there is an excluder character in the current value
    --      ->  If an excluder character is found, push the current value, 
    --          without the excluder, to the resulting array of excluding values
    --      ->  If no excluder is found, push the value to the resulting array
    --          of including values
    --      ->  The corresponding position variable is incremented accordingly
    while( LENGTH(:v_remaining) > 0 )
    do
        v_current := SUBSTR_BEFORE (:v_remaining,:v_delimiter);
        v_remaining := SUBSTR_AFTER (:v_remaining, :v_current || :v_delimiter);

        if substring(:v_current, 1, 1) = :v_excluder
        then
            a_exclude[v_pos_exclude] := SUBSTR_AFTER (:v_current, :v_excluder);
            v_pos_exclude := :v_pos_exclude + 1;

        else
            a_include[v_pos_include] := v_current;
            v_pos_include := :v_pos_include + 1;

        end if;
    end while;

    --> ASSIGNMENTS BLOCK
    -- format counter and divisions as table
    o_include = unnest(:a_include) AS ("VALUE");
    o_exclude = unnest(:a_exclude) AS ("VALUE");

    return 
        select  'I' as type, value from :o_include
        union all
        select  'E' as type, value from :o_exclude
        ;

END;

Upvotes: 0

Caio Melzer
Caio Melzer

Reputation: 130

Until the SP10, you need to create a function to SPLIT strings.

  • If your need is get some char, you can use SUBSTR()

  • On SP9 or later, you can work with REGEX

Upvotes: 2

Peder Rice
Peder Rice

Reputation: 1794

Another way of splitting a string is with an outbound variable using table types:

CREATE TYPE UTIL_VARCHAR_LIST AS TABLE
(
    VarcharValue Varchar(5000)
);

CREATE PROCEDURE UTIL_SPLIT_STRING
(
    IN iv_split_string Varchar(5000),
    IN iv_split_character Varchar(1) DEFAULT ',',
    OUT ot_string_list UTIL_VARCHAR_LIST
) LANGUAGE SQLSCRIPT
AS
BEGIN
    DECLARE TEMP_STR VARCHAR(5000) := :iv_split_string || :iv_split_character;
    DECLARE OUT_VAR VARCHAR(5000) ARRAY;
    DECLARE POS INTEGER :=1;
    DECLARE FLAG INTEGER := 1;
    DECLARE LEFT_STR VARCHAR(5000);

    WHILE(LENGTH(:TEMP_STR) > 0 )
    DO
        LEFT_STR := SUBSTR_BEFORE (:TEMP_STR,:iv_split_character);
        TEMP_STR := SUBSTR_AFTER (:TEMP_STR,:LEFT_STR || :iv_split_character);
        OUT_VAR[POS] := LEFT_STR;
        POS := :POS + 1;
    END WHILE;

    ot_string_list = UNNEST(:OUT_VAR) AS ("VARCHARVALUE");
END;

Upvotes: 2

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

Try this,

Refer Here

CREATE PROCEDURE SPLIT_TEST(TEXT nvarchar(100))
AS
BEGIN
  declare _items nvarchar(100) ARRAY;
  declare _text nvarchar(100);
  declare _index integer;
  _text := :TEXT;
  _index := 1;

  WHILE LOCATE(:_text,',') > 0 DO
  _items[:_index] := SUBSTR_BEFORE(:_text,',');
  _text := SUBSTR_AFTER(:_text,',');
  _index := :_index + 1;
  END WHILE;
  _items[:_index] := :_text;

  rst = UNNEST(:_items) AS ("items");
  SELECT * FROM :rst;
END; 

CALL SPLIT_TEST('A,B,C,E,F')

Upvotes: 7

Related Questions