SUBSTRING in ABAP

[Former Member]

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

[babu_kilari4]

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

[Former Member]

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

---

SELECT *
 FROM table
 where field = 'B'.

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

[former_member873340]

Martin

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

[Former Member]

Exactly i need this statement for SQl over 2 tables

e.g.

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

Martin

[babu_kilari4]

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

[uwe_schieferstein]

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

[Former Member]

HI,

Try this way..

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

[herzelhaimharel_gilor]

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' .

[Former Member]

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

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

Martin

[Former Member]

Hi,

Yes. You can use substrings in ABAP

try like this

select *

from table

where field like '_B%'.

Regards,

Jyothi CH.

.

[Former Member]

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

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

right ?

Martin

[babu_kilari4]

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

[Former Member]

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

[babu_kilari4]

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

[babu_kilari4]

Hello Martin,

Have you checked the program?? Was that helpful?

Thanks,

Babu Kilari

[Former Member]

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

[babu_kilari4]

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

[Former Member]

do u look for something like this

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

[former_member206725]

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

[Former Member]

Hello Renato,

It was a long time before :-), i suppose that i solved it by using abap code instead of SQL statement

Best Regards

Martin

[Former Member]

Hello,

I got it with new open SQL Syntax avalaible from enhancement 740.

For example:

Table ZHR_EXAMPLE_TABLE2 has the column ID_EMPLEADO with 6 characters instead of 8 like PERNR in table ZHR_EXAMPLE_TABLE1.

SELECT

T0~COLUMN1, T0~COLUMN2, T0~COLUMN3, T0~COLUM4, T1~COLUMN1 AS COLUMRESULT, T1~PERNR

FROM ZHR_EXAMPLE_TABLE1 AS T0

INNER JOIN ZHR_EXAMPLE_TABLE2 AS T1 ON SUBSTRING( T1~PERNR,3,6 ) = T0~ID_EMPLEADO

WHERE T0~IDPARTE = @ZIDPARTE AND T1~BEGDA LE T0~FECHA AND T1~ENDDA GE T0~FECHA

INTO CORRESPONDING FIELDS OF TABLE @ITAB.

IF SY-SUBRC EQ 0.

    <your code with the internal table itab>

ENDIF.

Instead of "INTO CORRESPONDING FIELDS OF TABLE @ITAB"  you can use "INTO TABLE @DATA(result)". With result no declarated before, the result of the select will be in the that table.

I hope it can be useful for someone.

Kind regards,

Julian.