topic Re: SUBSTRING in ABAP in Application Development and Automation Discussions

SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 08:04:08 GMT

Hi Experts,

Can i use the similar SQL statement as below in ABAP SQL or something as it ?

SELECT *

FROM table

where substring (field,1,1) = 'B'

Thanks in advance

Martin

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 08:08:05 GMT

Hi Martin,

Substring concept is nothing but the offset conept (Ex: x+2(3)) in ABAP.

You cannot use it directly in the where condition of an ABAP Statement.

Instead you need to manipulate it first and then pass it to the select statement.

Hope this is helpful

Thanks,

Babu Kilari

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 08:08:21 GMT

Use the following code as per ur requirement----

SELECT *
 FROM table
 where field = 'B'.

if field = field+1(2).
endif.
endselect.

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 08:12:00 GMT

Exactly i need this statement for SQl over 2 tables

e.g.

WHERE SUBSTRING ( table1_{field,1,1) = table2}field

Martin

Re: SUBSTRING in ABAP

uwe_schieferstein — Thu, 16 Apr 2009 08:13:24 GMT

Hello Martin

Of course you can use substrings but in a different syntax:


*&---------------------------------------------------------------------*
*& Report  ZUS_SDN_DB_SELECT_SUBSTRING
*&
*&---------------------------------------------------------------------*
*& Thread: SUBSTRING in ABAP
*& <a class="jive_macro jive_macro_thread" href="" __jive_macro_name="thread" modifiedtitle="true" __default_attr="1309280"></a>
*&---------------------------------------------------------------------*

REPORT  zus_sdn_db_select_substring.

TABLES: t001.

DATA: gs_t001   TYPE t001.


START-OF-SELECTION.

  BREAK-POINT.

" NOTE: '_' in SELECT statement means exactly 1 Character
" NOTE: '%' in SELECT statement means 0 or many Characters

" Select for all company codes having '1' in second place...
  SELECT * FROM t001 INTO gs_t001
    WHERE bukrs like '_1__'.

    write: / gs_t001-bukrs.
  ENDSELECT.
  skip.

  SELECT * FROM t001 INTO gs_t001
    WHERE bukrs like '_1%'.

    write: / gs_t001-bukrs.
  ENDSELECT.

END-OF-SELECTION.

Regards

Uwe

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 08:15:30 GMT

HI,

Try this way..

SELECT *
FROM table
WHERE FIELD LIKE '_B%'. ---> check the values which has B in second position.

Re: SUBSTRING in ABAP

former_member873340 — Thu, 16 Apr 2009 08:16:33 GMT

Martin

What exactly are you trying to achieve, can u explain a little more

Re: SUBSTRING in ABAP

herzelhaimharel_gilor — Thu, 16 Apr 2009 08:20:01 GMT

Hello Martin ,

you can try this way

select * from table

where field like 'B%' .

and you get in the result set all occurrences the starts with 'B' .

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 08:20:03 GMT

Hi Martin,

As you are using two tables field values you need to take them in to work area first and then manipulate for the specific character.

Then, you need to pass it on to the Where condition.

All the above examples suggested might work if you are working with one table.

Thanks,

Babu Kilari

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 08:21:22 GMT

Exactly - > i need run SQL statement over 2 SAP database tables with condition below

WHERE SUBSTRING ( table1_{field,1,1) = table2}field

Martin

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 08:23:01 GMT

Hi,

Yes. You can use substrings in ABAP

try like this

select *

from table

where field like '_B%'.

Regards,

Jyothi CH.

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 08:24:55 GMT

The result - > i CANNOT use this SQL statement (with substring) directly over 2 TABLES

WHERE SUBSTRING ( table1_{field,1,1) = table2}field

right ?

Martin

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 08:27:14 GMT

Martin,

As far as I know, you cannot use it directly over two tables. But, I think it can be done with bit of manipulation logic. Will get back to you if I get something out of my mind.

Thanks,

Babu Kilari

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 08:39:18 GMT

Hi Martin,

Try concatenating the value of the second table in a field and then run the select query.

Try sumthing like this:

CONCATENATE '_' table2~field '%' into lv_test.

SELECT * FROM t001 INTO TABLE gs_t001

WHERE bukrs like lv_test.

Regards,

Surinder

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 09:06:45 GMT

Martin,

I think you can implement it this way.

I have developed a small program meeting your requirement. You can try it out.

REPORT  ZSAMPLE_SDN7.

DATA: temp(1) TYPE c.

types :begin of table1,
       field(10) type c,
       end of table1.


types :begin of table2,
       field(1) type c,
       end of table2.

types :begin of table3,
       field(10) type c,
       end of table3.

data: t_table1 type STANDARD TABLE OF table1,
      t_table2 TYPE STANDARD TABLE OF table2,
      t_table3 TYPE STANDARD TABLE OF table3,
      wa_table1 type table1,
      wa_table2 type table2,
      wa_table3 type table3.

move 'abcdef' to wa_table1.
append wa_table1 to t_table1.

move 'abdghf' to wa_table1.
append wa_table1 to t_table1.

move 'cbdefk' to wa_table1.
append wa_table1 to t_table1.

move 'fgegrg' to wa_table1.
append wa_table1 to t_table1.



move 'b' to wa_table2.
append wa_table2 to t_table2.

move 'b' to wa_table2.
append wa_table2 to t_table2.

move 'c' to wa_table2.
append wa_table2 to t_table2.

move 'g' to wa_table2.
append wa_table2 to t_table2.



START-OF-SELECTION.

sort t_table2 by field.
delete ADJACENT DUPLICATES FROM t_table2.

loop at t_table2 into wa_table2.

loop at t_table1 into wa_table1.

  temp = wa_table1+1(1).
  if wa_table2-field eq temp.
  append wa_table1-field to t_table3.
  endif.
clear: wa_table1.
endloop.

clear: wa_table2.
endloop.

END-OF-SELECTION.

loop at t_table3 into wa_table3.
write:/ wa_table3.
endloop.

Please let me know if that helps.

Thanks,

Babu Kilari

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 09:24:40 GMT

Hello Martin,

Have you checked the program?? Was that helpful?

Thanks,

Babu Kilari

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 09:27:10 GMT

Thanks Babu

for your example, but i wanted to use SQL over database tables with something as SUBSTRING

i have to change logic of the solution , because ABAP doesn't know something as SUBSTRING in SQL

Martin

Re: SUBSTRING in ABAP

Former Member — Thu, 16 Apr 2009 09:31:36 GMT

Martin,

You are welcome

I understood the problem and That is the reason I have developed the program similar to your requirement.

Consider t_table1 as first table has field that contains many characters

Consider t_table2 as second table which has only 1 character field

t_table3 will have the data fetched from t_table1 for the condition you have mentioned.

Hope you will explore the example to understand it better way.

Thannks,

Babu Kilari

Re: SUBSTRING in ABAP

Former Member — Mon, 08 Feb 2010 09:41:28 GMT

do u look for something like this

from your_table where SUBSTRING(`A_COLUMN`,0,4) = '/2/3'

Re: SUBSTRING in ABAP

Former Member — Fri, 13 Dec 2013 06:07:12 GMT

Hi Martin,

I share your frustration.

Having said that, have you found an answer for your original question?

Is there such a thing like the good old oracle ANSI SQL Substr function?

(please everyone: do not provide an answer related to %, *, _ or any other character substitution with constants. I am looking for functions over two database columns)

Cheers

Renato