code to split data in a string

[Former Member]

Hi Team,

           I have a table ( Lets say T1) with 3 columns as below. 

          I have inserted some data as below

Rnum	DOCID	RDATE
CQM4912	can_quebec_montreal_hotel_xixe_siecle	May 3 2005
IN1067	india_new-delhi_hotel_clark_international	Apr 7 2007
UNY5272	usa_new-york-city_best_western_convention_center_hotel	Nov 8 2006
UN8125	usa_nevada_las-vegas_gold_spike_hotel_and_casino	Jul 13 2006

I  have created another table (Lets say T2) with below structure.

My requirement is, i need to divide the data of DOCID column of table T1 into COUNTRY, REGION, CITY, HOTEL columns in table T2.  Challenge here is, data is not in a proper pattern  (i.e., COUNTRY, REGION, CITY, HOTEL).

For example, from above sample records, 

Record 1:  It has our required pattern of COUNTRY, REGION, CITY, HOTEL.  Therefore if we split it then we get COUNTRY = can, REGION = Quebec, CITY = Montreal, Hotel = hotel xixe siecle

Record 2:  It doesn't follow our required pattern.  In this case COUNTRY = ind, REGION = empty CITY = new delhi,  HOTEL = hotel clark international

Record 3: It is similar to record 2, where REGION is blank.

Record 4: It is in our required pattern. 

1) First thing is, i need to know, how can i split the string of DOCID of T1  into COUNTRY, REGION, CITY, HOTEL columns of T2 table.

2) Second thing is, since data is not always in our required pattern ( in most cases, REGION would be missing),  how could be split it in the right columns.

Would appreciate your help and support on this.

Regards,

Syed

[former_member200930]

Hi,

You can use following Syntax to get proper records in new table.

Create fulltext Index "<<Your_Schema_Name>>"."<<Index Name>>" on "<<Your_SCHEMA_NAME>>."T1" ("DOCID") ASYNC FLUSH EVERY 1 MINUTES LANGUAGE DETECTION ('EN') TEXT ANALYSIS ON;

So, you will get new table in your schema.

This will solve your problem.

[muthuram_shanmugavel2]

Hi Syed,

This can be done using HANA Text Analysis by creating custom dictionaries and rules.

Please refer this link.

https://open.sap.com/courses/hsta1/items/1zDHPSfHNjY0iSmI69R9Mu

Regards,

Muthuram

[Former Member]

Hi.

1) First thing is, i need to know, how can i split the string of DOCID of T1  into COUNTRY, REGION, CITY, HOTEL columns of T2 table.

- try to use regexp for strings

2) Second thing is, since data is not always in our required pattern ( in most cases, REGION would be missing),  how could be split it in the right columns.

- for right splitting you need to check every word  (For example - create dictionary)