1

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 SQLScript?

Note that I am novice on creating store procedure.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Boy12343
  • 75
  • 3
  • 10
  • 6
    Storing comma separated values in a single column is a bad idea to begin with. Read up on database normalization –  Aug 27 '18 at 13:35
  • 2
    It looks like there is an `instr()` function available, but I'll be damned if I can find documentation from help.sap.com (which is barely functioning right now anyway). Perhaps try something like `Left(yourfield, Instr(Substr(yourfield, 10, Length(yourfield)-10) + 10, ","))` Basically finding the first comma that appears at position 10 or later and use that position to determine the second parameter of your `Left()` function. I've never used Hana, so I may be a bit off with the syntax here. – JNevill Aug 27 '18 at 13:47
  • 1
    @JNevill is right! The function is called [`LOCATE`](https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20e3b6b77519101485e6bd62f7018f75.html?q=locate) and can start looking for the dangling `,` from the right side of the string by providing -1 as the starting position. The SQL expression would then be something like `LEFT ( , LOCATE(LEFT(, 10), ',', -1) -1)` – Lars Br. Aug 28 '18 at 02:37
  • Thank you @JNevill and Lars-br It works perfectly. – Boy12343 Aug 28 '18 at 09:08
  • @JNevill you should convert this comment into an answer so that this question can be closed. – Lars Br. Aug 28 '18 at 23:35

1 Answers1

0

One option is a long case when statement. It will look at the 11th character, if it is a comma, we want exactly the first 10 digits, if it is not, we'll look at the 10th digit, if that is a comma, we get the first 9 digits, if it is not, we'll look at the 9th and get the first 8 digits and so on...

You can shorten this check if the numbers that make up your strings are always a certain length or less, but this will check each individually

case when right(left(list_of_numbers,11),1)=','
    then left(list_of_numbers,11)
when right(left(list_of_numbers,10),1)=','
    then left(list_of_numbers,10)
when right(left(list_of_numbers,9),1)=','
    then left(list_of_numbers,9)
when right(left(list_of_numbers,8),1)=','
    then left(list_of_numbers,8)
when right(left(list_of_numbers,7),1)=','
    then left(list_of_numbers,7)
when right(left(list_of_numbers,6),1)=','
    then left(list_of_numbers,6)
when right(left(list_of_numbers,5),1)=','
    then left(list_of_numbers,5)
when right(left(list_of_numbers,4),1)=','
    then left(list_of_numbers,4)
when right(left(list_of_numbers,3),1)=','
    then left(list_of_numbers,3)
when right(left(list_of_numbers,2),1)=','
    then left(list_of_numbers,2)
else left(list_of_numbers,1) end as result

the else assumes the string starts with a single digit number.

Golden Ratio
  • 349
  • 2
  • 14