Chineese charcter Substring in SAP HANA

[former_member16553]

Hi Expert,

We need to perform substring function in a calculated column which contains data in Chinese.

we tried with leftstr function but it's returning junk value also tried with ROWTOHEX but no luck.

Could you please advice how we can do it?

Thanks,

Girdhari

[lucas_oliveira · Accepted solution]

Hi Girdhari,

I just found a note that explains your scenario: 2252224.

So for your case you might want to use midstru instead of leftstr (or sql left if you want to change from Column to SQL expression) and keep with column engine expressions. I believe I could reproduce your scenario and correct it:

LEFT_SQL is SQL Engine Expression function left("COL1",2) - good

MIDSTRU_COL is Column Engine Expression function midstru("COL1",1,2) - good

LEFTSTR_SQL is Column Engine Expression function leftstr("COL1",2) - bad

Best Regards,

Lucas de Oliveira

[former_member16553 · Accepted solution]

Hi Lucas,

MIDSTRU worked in our case.

Thanks a lot for your help.

Thanks,

Girdhari

[lucas_oliveira]

Hi,

I could recreate the scenario but could not recreate your issue. Please provide the ddl of your table and a few lines as well as the calculated column expression and datatype you're using.

For the scenario below if worked fine:

create column table SYSTEM.CHINA (col1 nvarchar(50));
insert into CHINA values ('歡迎光臨'); 
insert into CHINA values ('你貴姓大名？'); 
insert into CHINA values ('我很高興跟你見面'); 
select *, length(col1), left(col1,2) from CHINA;

I got the following result set for this test:

COL1    ;LENGTH(COL1);LEFT(COL1,2)
歡迎光臨    ;4           ;歡迎          
你貴姓大名？  ;6           ;你貴          
我很高興跟你見面;8           ;我很        

I then created a a calc view with the following calculated column (typed as NVARCHAR also, obviously) :

--calc attribute expression
left("COL1",2)  

--results
COL1    ;LEFTY;VAL
歡迎光臨    ;歡迎   ;1  
你貴姓大名？  ;你貴   ;1  
我很高興跟你見面;我很   ;1  

Provide more details so we can try helping.

Regards,

Lucas de Oliveira

[former_member16553]

Hi Lucas,

Just want to clear another point changing vocabulary expression is switching the engine or something else.

If it's not switching the engine, can you please share the steps to change the vocabulary expression to SQL engine.

Thanks,

Girdhari

[former_member16553]

Hi Lucas,

Thanks for your reply.

We are in SPS12. We tried the LEFT function by switching to SQL engine but didn't work. The base table is a custom table and column data- type NVARCHAR. I have attached the screen-shot for your reference.

Original value Substring

Thanks,

Girdhari

[lucas_oliveira]

Hi Girdhari,

If you're using Column Engine function, then the function you want to call is leftstr and no left. The later can only be used if you switch the vocabulary of your expression language to 'SQL'. This option is available from SP11 on if I'm not mistaken.

If that does not work, share your catalog information and data so we can check what could be wrong.

Regards,
Lucas de Oliveira

[former_member16553]

Hi Lucas,

Thanks for your reply.

In our HANA system LEFT function is not working. We doing it in a cal view-calculated column. Please find attached screen shot for your reference.

Thanks,

Girdhari