Find numbers in a string

[former_member215819]

Hi Experts,

I am struggling to get numbers from a string value. Here's the requirement:

sample data in table column:

I have 52 pens

My 52 pens are brand new

Total number of pens is 52

for each row of above sample - i need to get the value '52'

Saw some functions suggesting how to implement a isNumeric check but here i need to pick the numeric value only

from a bunch of words and not just check if it is numeric data or not.

Any suggestions on what can be done?

Thanks

Regards

VN

[former_member405173]

This message was moderated.

[sreehari_vpillai]

Lars Said : Any chance of using regular expression? | SCN

Couldn't you apply this functionality in xsjs context ? From where your data is consumed ?

Sree

[former_member215819]

No, I need to use the number value in further multiple look ups (2-3 tables data) with other column data involved and get another numeric value for final calculation.

[former_member182302]

Hi Vidya,

See the reply from Lars in this thread:

Regards,

Krishna Tangudu

[former_member215819]

Krishna,

I have checked Lars link on checking for numeric values - and it doesn't help my scenario.

Thats what i was referring to with is Numeric check on my issue description.

Any other suggestions?

VN

[lbreddemann]

Hi Vidya,

I recommend to revisit the SQL documentation for SAP HANA SPS 9.

You'll find that regular expressions are now supported in there.

For example LOCATE_REGEXPR - SAP HANA SQL and System Views Reference - SAP Library lets you look for occurrences of reg. expressions via the WHERE clause.

- Lars

[former_member215819]

Hi Lars

Yes, i did check that syntax ..unfortunately we are on SP8 and hence am unable to use these.

Also ... though my example shows same number "52" on all three rows. In actual scenario we have data with different numbers, 52 - 10 , etc. as well.

Any other recommendations?

Thanks

VN

[lbreddemann]

For versions < SPS 9 there is no build in feature available (that I know of) that would yield your desired result.

Regular expressions are there to accommodate exactly this kind of requirement.

So, if you want to do this ad hoc, within a SQL statement on SPS 8, you'll have to build this yourself.

Other than that, you may consider finding this via during data loading.

A last option that comes to my mind is to use text analysis with a custom configuration. Not sure if this is supported with SPS 8, though.

- Lars

[former_member182302]

Hi Vidya,

I think as you are in SP8 you cannot use reg exp.

I was sharing the link so that you would be able to create your own custom function in the similar lines.something like below:

drop function returnNumeric;
create function returnNumeric( IN checkString NVARCHAR(300))
returns returnNumeric NVARCHAR(300)
language SQLSCRIPT as
begin
declare tmp_string nvarchar(300) := :checkString;
declare empty_string nvarchar(1) :='';

/* replace all numbers with the empty string */
tmp_string := replace (UPPER(:tmp_string), 'A', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'B', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'C', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'D', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'E', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'F', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'G', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'H', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'F', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'G', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'H', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'I', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'J', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'K', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'L', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'M', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'N', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'O', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'P', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'Q', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'R', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'S', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'T', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'U', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'V', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'W', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'X', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'Y', :empty_string);
tmp_string := replace (UPPER(:tmp_string), 'Z', :empty_string);

/*if the remaining string is not empty, it must contain non-number characters */


returnNumeric := :TMP_STRING;

end;

select  returnNumeric('52 pens')  from DUMMY;

52

See if this works for you.

Regards,

Krishna Tangudu

[Michał]

Hi Vidya,

in case of non-alphanumeric characters in your strings, you may take and a bit extend Krishna's valid scalar UDF to remove all non-numeric characters as shown below

CREATE FUNCTION returnNumeric ( IN checkString NVARCHAR(300) ) 
  RETURNS returnNumeric NVARCHAR(300)  
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER AS
BEGIN
  DECLARE pos, len INT;
  DECLARE tmp_string nvarchar(300) := :checkString;  
  returnNumeric := '';

  -- remove ^[1-9]
  pos := 1;
  len := LENGTH(tmp_string);
  WHILE (pos <= len ) DO
    IF ( ASCII(SUBSTRING(tmp_string,pos,1)) >= 48 AND ASCII(SUBSTRING(tmp_string,pos,1)) <= 57 ) THEN
      returnNumeric := returnNumeric || SUBSTRING(tmp_string,pos,1);
    END IF;
    pos := pos + 1;
  END WHILE;
END;

Best regards,

Michal