cancel
Showing results for 
Search instead for 
Did you mean: 

Find numbers in a string

0 Kudos
1,102

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

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

This message was moderated.

sreehari_vpillai
Active Contributor
0 Kudos

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

0 Kudos

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
Active Contributor
0 Kudos

Hi Vidya,

See the reply from Lars in this thread:

Regards,

Krishna Tangudu

0 Kudos

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
Active Contributor
0 Kudos

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

0 Kudos

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
Active Contributor
0 Kudos

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
Active Contributor
0 Kudos

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ł
Advisor
Advisor
0 Kudos

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