Full text search

[bharath_padmanabhan]

Experts,

I am creating a Odata service where the input will be Company code description(T001-BUTXT).

Let us assume user gives the description as 'hold'.

Now I have to fetch 2 records from T001,

Record 1:

Company code: 1000

Company code Description: Indian Holding

Record 2:

Company code 2000

Company code Description: American Hold.

I see that SAP has created Full text index for BUTXT field. I want to use CONTAINS keyword to implement Full text search. But when I type CONTAINS keyword after WHERE in select query, it's not recognized. I searched the forum for an example code. But I see all examples pointing to HANA DB level coding using ADBC, AMDP etc.

Can we use Full text search in ABAP Editor(SE38 SAP GUI)?

Thanks

Bharath

[Sandra_Rossi]

You should read the ABAP SQL documentation, CONTAINS doesn't exist. The classic SQL operator is LIKE.

Do you mean this?

SELECT * FROM T001 WHERE BUTXT LIKE '%case-sensitive text%'.

NB: it doesn't require an index to work. T001 is the table of company codes so it contains few entries.

NB: to have a case-insensitive search, define a new column with the function LOWER or UPPER, and search the text literal either lower case or upper case respectively.

[bharath_padmanabhan]

Hi Sandra,

Thank you so much for your reply. I was referring to Full text search as referred in this Blog Post

I am sorry for posting a thread here without calmly reading the Full Text help. I re-read it now and I see that Full text search is not supported in Open SQL yet. We have to go with ADBC as seen in the attachment. full-text-1.png full-text-2.png

Scenario 1: User input for Company code description - Hold

Approach: I use pattern search using % operator as you suggested and I am able to fetch American Hold as well as Indian Holding records.

Scenario 1: User input for Company code description - hold

Approach: I use pattern search using % operator. But this time it does not return any record as the letter h in hold is small.

Are you suggesting that I should add a new field in T001 table and store the Company code text in Lower or Upper case(As desired) and run my query against this custom field in T001 table?

On a side note - I am getting notifications when someone replies to my questions. Yay! Last time you suggested me to add a tag to achieve this. I tried that a few times but it still didn't help. To my surprise, notifications are working fine now.

Thanks

Bharath

[bharath_padmanabhan]

If we introduce a new custom field in T001 field to store the Company code description in Upper or Lower case, then shouldn't we have to manage to populate this custom field always from the standard Tcodes that update T001 table?

[Sandra_Rossi]

Thanks for clarifying. I agree, as you say, the blog post you are referring to is about SAP HANA SQL, not about ABAP (but using HANA SQL from ABAP is possible via ADBC, AMDP, EXEC SQL...)

My answer was not referring to HANA SQL, it was pure ABAP SQL.

Concerning the case-insensitive search, I'm not proposing to create a column in the table, only to define one at runtime directly in the SELECT.

ABAP >= 7.51:

WITH +t001 AS ( SELECT upper( butxt ) AS upper_butxt
             FROM t001 )
    SELECT *
    FROM +t001
    WHERE upper_butxt LIKE '%UPPER_CASE%'
    INTO TABLE @DATA(xx).

[former_member793745]

Hi, You can use short form of 'Contains' like CN or CO. Please check the abap docu.I think, we can use in ABAP SQL .

Rgds,

Khadeer Basha

[bharath_padmanabhan]

Hi Khadeer,

My actual requirement is to use Full text search feature where I can pass a string against Description field and get all the matches. It also gives you the score and you can display the top 3-5 records based on highest match score.

In a regular pattern search using any operator like CN or CO - This is not doable.

Since I can't do full text search in Open SQL, I am going with LIKE. The problem still remains which is that the search using CO or CN or Like will be case sensitive. If you have a record with description as India and if you search with the text 'india', no results will be returned.

I would like to know how we can workaround this problem and perform case insensitive search as per the option which Sandra suggested.

Thanks

Bharath

[Sandra_Rossi]

CO and CN are not part of ABAP SQL.

Without considering SQL, "contains" would be best achieved using CP (contains pattern). Note that CP is by default case-insensitive (while CO/CN are case-sensitive, but anyway they have a different use case, working efficiently to find the presence of individual characters, not full text search).

IF variable CP '*case-insensitive text*'.
  ...
ENDIF.

[former_member793745]

Bharat, still you can work with CP, as you need to fetch all the descriptions matching with the given 'description field value', as CP will fetch you all the values matching the given pattern and also CP is case insensitive. Please try this.

[bharath_padmanabhan]

Hi Khadeer,

Thanks a lot for your reply. You and Sandra are correct about pattern search being case insensitive. I am sorry for posting earlier that it is case sensitive.

I tried the code using CP operator and we are able to achieve the requirement using this alternative solution as well.

I can't wait to use fuzzy. linguistic and full text search using Open SQL someday.

Thanks to you both for taking your valuable time to help me out.

Cheers,

Bharath

[bharath_padmanabhan]

Sandra,

I have added a distinct answer, but I could simply have edited the original one. Just ask me to do it.

Can you please edit the original one. Your solution helped me solve the problem and I would like to accept your solution as the answer.

Thanks

Bharath

[Sandra_Rossi]

I have added a distinct answer, but I could simply have edited the original one. Just ask me to do it.

[Sandra_Rossi]

You should read the ABAP SQL documentation, CONTAINS doesn't exist. The classic SQL operator is LIKE.

Do you mean this?

SELECT * FROM T001 WHERE BUTXT LIKE '%case-sensitive text%'.

NB: it doesn't require an index to work. T001 is the table of company codes so it contains few entries.

NB: to have a case-insensitive search, define a new column with the function LOWER or UPPER, and search the text literal either lower case or upper case respectively. SEE BELOW.

---------

Your reply:

Thank you so much for your reply. I was referring to Full text search as referred in this Blog Post

I am sorry for posting a thread here without calmly reading the Full Text help. I re-read it now and I see that Full text search is not supported in Open SQL yet. We have to go with ADBC as seen in the attachment.

Scenario 1: User input for Company code description - Hold

Approach: I use pattern search using % operator as you suggested and I am able to fetch American Hold as well as Indian Holding records.

Scenario 1: User input for Company code description - hold

Approach: I use pattern search using % operator. But this time it does not return any record as the letter h in hold is small.

Are you suggesting that I should add a new field in T001 table and store the Company code text in Lower or Upper case(As desired) and run my query against this custom field in T001 table?

On a side note - I am getting notifications when someone replies to my questions. Yay! Last time you suggested me to add a tag to achieve this. I tried that a few times but it still didn't help. To my surprise, notifications are working fine now.

---------

My reply:Thanks for clarifying. I agree, as you say, the blog post you are referring to is about SAP HANA SQL, not about ABAP (but using HANA SQL from ABAP is possible via ADBC, AMDP, EXEC SQL...)

My answer was not referring to HANA SQL, it was pure ABAP SQL.

Concerning the case-insensitive search, I'm not proposing to create a column in the table, only to define one at runtime directly in the SELECT.

ABAP >= 7.51:

WITH +t001 AS ( SELECT upper( butxt ) AS upper_butxt
             FROM t001 )
    SELECT *
    FROM +t001
    WHERE upper_butxt LIKE '%UPPER_CASE%'
    INTO TABLE @DATA(table_t001).