How to increment number part of string in SAP hana sql

[former_member597564]

I am working on one requirement where I need to take the maximum value of a column with string data type and increment by one everytime when there is a new entry in table.

For example.

Abc_current_date_5...

This should be incremented to Abc_current_date_6

The number part should be fetched from the table everytime and should be incremented by 1

How can we achieve this ?

[pfefferf]

What value your string field can have? Is it ensured that only the number to be incremented is a digit in your string or is it possible that the string contains other digits (e.g. "Abc_20190119_test_4")?

Maybe you can tell us in addition what you wanna reach here and why this is necessary. For me this (incrementing a number in a string) sounds like the functionality/persistence is designed in a wrong way. If the requirement is to visualize that in such a way for the end user ok, but this does not mean that the persistence needs to store that in such a way.

[former_member597564]

Hi Florian,

String is one of the key field in a table.

Format of the string : ABC_ID_Currentdate_number part

For every InSert in table,number increases by 1

ABC_ID_Currentdate_1

ABC_ID_Currentdate_2

ABC_ID_Currentdate_3

and so on.......

Everytime it should fetch last value of string and increment by 1

[pfefferf]

What means ID and Currentdate in your string. Are that just placeholders for an Integer and a Date value or are these real string values?

[former_member597564]

ID is just a string and Currentdate stands for todays date it changes everytime for new record

[pfefferf · Accepted solution]

Although I still believe that the design is wrong (as mentioned in comments), there are options to do that. For instance I created following scalar function which first splits the string in the "fix" part and in the number part. Then the number is increased and the result string with the increased number is created.

Function (the comment lines is the coding in case your HANA version does not already support executing the expressions/functions w/o doing it via a Select):

FUNCTION "increaseLastNumberInString" ( in iv_string nvarchar(5000 ) )
	RETURNS ev_string nvarchar(5000)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN
   declare lv_regex nvarchar(256) := '\d+$';
   declare lv_extract_fix_val nvarchar(5000);
   declare lv_extract_num nvarchar(5000);

   lv_extract_fix_val = substring(:iv_string, 1, locate_regexpr(:lv_regex in :iv_string) - 1);
   --select substring(:iv_string, 1, locate_regexpr(:lv_regex in :iv_string) - 1) into lv_extract_fix_val from dummy;
   lv_extract_num = substring_regexpr(:lv_regex in :iv_string);
   --select substring_regexpr(:lv_regex in :iv_string) into lv_extract_num from dummy;

   ev_string = :lv_extract_fix_val || (to_integer(:lv_extract_num) + 1);
   --select :lv_extract_fix_val || (to_integer(:lv_extract_num) + 1) into ev_string from dummy;
END;

Some tests:

select "increaseLastNumberInString"( 'ABC_ID_20190101_0' ) from dummy;  " returns ABC_ID_20190101_1
select "increaseLastNumberInString"( 'ABC_ID_20190101_40' ) from dummy; " returns ABC_ID_20190101_41