Skip to Content
0

Chineese charcter Substring in SAP HANA

Nov 14, 2016 at 01:27 PM

85

avatar image

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

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

6 Answers

Best Answer
avatar image
Former Member
Nov 17, 2016 at 07:26 PM
0

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


left-midstru.png (10.4 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Lucas,

MIDSTRU worked in our case.

Thanks a lot for your help.

Thanks,

Girdhari

0
avatar image
Former Member
Nov 15, 2016 at 02:10 AM
1

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

Share
10 |10000 characters needed characters left characters exceeded
girdhari mondal Nov 15, 2016 at 06:42 AM
0

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


Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member
Nov 16, 2016 at 04:42 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
girdhari mondal Nov 16, 2016 at 06:26 AM
0

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


Share
10 |10000 characters needed characters left characters exceeded
girdhari mondal Nov 16, 2016 at 06:50 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded