Skip to Content

Chineese charcter Substring in SAP HANA

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    Nov 17, 2016 at 07:26 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 15, 2016 at 02:10 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 15, 2016 at 06:42 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 16, 2016 at 04:42 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 16, 2016 at 06:26 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 16, 2016 at 06:50 AM

    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

    Add comment
    10|10000 characters needed characters exceeded