Skip to Content
0

Extract numbers from a string after a matching string occurence

Apr 23 at 08:02 AM

27

avatar image

Dear Experts,

I have a table which contains comments field against each material entry. I have to extract the numbers after the string "AVAR" that means

Example row:

Material X - 'this product 123 AVAR0001256701 manufactured in AVAR0001256725'

Expected output:

 X       0001256701
 X       0001256725

I tried with following

Select REPLACE_REGEXPR('\D' IN 'this product 123 AVAR0001256701 manufactured in AVAR0001256725' WITH '_') From Dummy;

but this returning output like

_____________123_____0001256701_____________________0001256725

Select Substr_REGEXPR('([0-9]+)' IN 'this product 123 AVAR0001256701 manufactured in AVAR0001256725' ) From Dummy;
This is returning me first occurrence of the number i.e. 123

I need to split them into rows.

Could anyone help me how to achieve this?

Thanks in advance!

With regards,

Naru.

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
Apr 23 at 09:51 AM
0

Hello Naru,

one simple approach would be to use a fulltext index on your comment column with the fulltext index configuration "LINGANALYSIS_BASIS". With that tokenization is applied to your comments, which means that each word is available in the fulltext index table as separate line. With that you can extract all lines starting with AVAR and remove AVAR from the content using the LTRIM function.

You can also create your own fulltext index configuration which extracts only the relevant information for you. For that I recommend to have a look in the SAP HANA Text Analysis Developer Guide.

Quick notes regarding the creation and usage of the fulltext index.

1) Create fulltext index

CREATE FULLTEXT INDEX <your_fulltext_index_name> ON <your_schema>.<your_table> (<your_comment_column_name>)
TEXT ANALYSIS ON
CONFIGURATION 'LINGANALYSIS_BASIC';

2) Search for the created fulltext index table and apply the following query:

SELECT LTRIM(TA_TOKEN, 'AVAR') AS COMMENT_ID 
FROM <your_schema>.<your_name_of_the_fulltext_index_table>
WHERE TA_TOKEN LIKE 'AVAR%';

Regards,
Florian

Share
10 |10000 characters needed characters left characters exceeded