How to truncate only numbers from an alpha numeric text field in calculated column of graphical calculation view

[amlan_dhow]

Hi all,

I have to implement the following logic in an HANA calculation view preferably in Graphical calculation view.

I have a field like BKTXT which contains either number or text or both and datatype of the field is VARCHAR.

Requirement is to create a calculated column:

• If BKTXT starts with a number, convert the numeric part to a number (ex. If BKTXT= “345 – Absolute”, result should be 345)
• If BKTXT contains only text or it starts with a text and not a number output should be 1 (ex. If BKTXT= “SCM DV” or “SCM DVI 57”, result should be 1)
• If BKTXT is Blank or ‘Null’, then result should be considered as Zero (0)

How can I implement this logic?

Here was the algorithm I thought of though unable to implement so far:

1. To find whether the first character is between 0 to 9 or not.
2. If no, then result is 1.
3. If yes, then take that. Then check for the 2^nd one and so on till it is between 0 to 9 and concatenate the same.

I am facing challenge to identify a numeric field in character field. If I can do that also how to run a loop here to take our a 3 digit or more number from the text field

Thanks in advance.

[Former Member]

Why Loop ?

Use select case for part of it, andcombine with regex

IF You have sps09 you cyn use regex...something like

select substring_regexpr( '(\d+)' IN 'r375' ) "broj" from DUMMY;

of course you'll have IN BKTXT  ...and from is some complex or not so complex join.

SUBSTRING_REGEXPR - SAP HANA SQL and System Views Reference - SAP Library

[amlan_dhow]

Hi Dubravko,

Thank you for your response. But we are using SPS08 which does not allow to use the REGEX.

Any solution considering SPS08 would be helpful.

Thanking you,

Regards,

Amlan

[Former Member]

>I am facing challenge to identify a numeric field in character field

If this is the only chalange then you can use ISNumeric equivlent someone wrote