SQL HANA STRING FUNCTION

[ashraf19881]

I have a table with a column LIST_OF_NUMBERS containing the following strings:

10, 20, 395, 443, 534, 734, 954, 105, 156

I want to truncate the LIST_OF_NUMBERS column to 10 characters as follows:

LEFT(LIST_OF_NUMBERS,10)

10, 20, 39

However, if a number from the list of string is partially truncated I want to truncate the whole number instead. For example in my case I do not want to display 39 as it's misinterpreting. I want to truncate the whole number as follows:

10, 20,

I believe it can be achieved with the following condition:

If the string does not ends with comma, truncate the strings until it ends with a comma.

How can I translate this condition in sql script?

Note that I am novice on creating store procedure.

[former_member188958]

What do you want to have happen if the list starts with a number with 10 or more digits?

[ashraf19881]

It will never happens. Maximum digits for a number is 4.

[pfefferf]

One way to solve that is like following:

do
begin
  -- prepare test data
  lt_test = select '10, 20, 395, 443, 534, 734, 954, 105, 156' as list_of_numbers from dummy
            union all select '10, 20, 36, 443, 534, 734, 954, 105, 156' as list_of_numbers from dummy
            union all select '10, 20, 4, 443, 534, 734, 954, 105, 156' as list_of_numbers from dummy;
  
  -- restrict to max 10 characters w/o cutting numbers in the middle
  select case when length(list_of_numbers) > 10 
           then 
             case when locate_regexpr('[0-9]' in substring(list_of_numbers, 11, 1)) > 0 
               then
                 substring(list_of_numbers, 1, locate(left(list_of_numbers, 10), ',', -1))
               else
                 left(list_of_numbers, 10)
             end
           else left(list_of_numbers, 10) end
  from :lt_test;
end;

What does the script basically?

• Check if the string is longer than 10 charaters, if not use the the max. available charaters.
• If the string is longer than 10 characters, it is checked if at the 11th position a number is available (to check if a number would be cut off). If not use the first 10 characters.
• If the character at the 11th position is a number, then the last ',' is searched within the first 10 characters. With that information than the substring can be taken from the first character to the character of the last found ',' in the first 10 characters.

Regards,
Florian