Regular Expressions

[Former Member]

Hi

Is it possible to use Regular Expressions in select queries? I need to give condition for a field in the select query like "which begin with a character followed by an undercore".

thanks and regards

neelima

[MarcinPciak]

I don't think it is possible on DB side. In queries you can only use LIKE or IN directly, but no regex.

You can however validate the entry with regex on Application Server side (once data are transported from the DB to a target structure). This however may have impact on query performance as you need to transport each single line from the table and skip those unnecessary. If the table is not too large and you do such query not to often then can you try it though. So you would need

select .... from table .... into wa_structure where ...
    ..."here validate wa_structure field against regex 
    check valid = 'X'.
   ....
endselect.

Regards

Marcin

[bbalci]

Hello Neelima,

You can use a range and CP operator for this,

Here is a select example to read KNA1-NAME1 field beginning with A and underscore :

REPORT x.

TABLES kna1.

RANGES :

r_name1 FOR kna1-name1.

START-OF-SELECTION.

r_name1-sign = 'I'.

r_name1-option = 'CP'. "<--- this is "covers pattern" operator

r_name1-low = 'A_* '. "<--- here is A_* search pattern

APPEND r_name1.

SELECT * FROM kna1

WHERE name1 IN r_name1.

"Your selection code

ENDSELECT.

I hope it helps.

Bulent

Edited by: Bulent Balci on Jul 23, 2010 1:34 PM

[Former Member]

hi Neelima,

I think Marcin's way will work, BUT the character A is hardcoded,

I think I have another way to do the same thing,

please try this way:

select *
  from kna1
  into TABLE lt_kna1
  WHERE NAME1 NE ''.

IF LT_KNA1[] IS NOT INITIAL.

loop at LT_KNA1 INTO WA_KNA1.
  IF WA_KNA1-name1+1(1) = '_'.
    WA2_KNA1 = WA_KNA1.
    APPEND WA2_KNA1 TO LT2_KNA1.
    CLEAR WA2_KNA1.

  ENDIF.
ENDLOOP.
ENDIF.

[Former Member]

Hi

thanks for the reply. I cant get all the details n do validation in appl serever as the DB table having large data n frequently changed.

[MarcinPciak]

Small modification using Bulent's code

r_name1-sign = 'I'.
r_name1-option = 'CP'. 
" + represents any character, _ - must be on second position, * - any number of character string
r_name1-low = '+_*'.   
APPEND r_name1.
...
select ....
   where field in r_name1.

Try it out. Likely to work.

Regards

Marcin

[Former Member]

Thanks Marcin

Its working.. But the problem is its taking even if the first character is number also. like '1_********'. I need to avoid this also.. any suggestion?

[MarcinPciak]

If you already have a pattern which filters out all unmatched entries, you can validate this one at App Serv side as the number for such result entries will be significantly lower. So In fact major filter will be on DB side, but number filtering at that position on App Serv.

Regards

Marcin

[bbalci]

Hi,

You can resctrict it in your select statement like that :

SELECT * FROM kna1

WHERE name1 IN r_name1.

if not (

kna1-name1(1) eq '1' or

kna1-name1(1) eq '2' or

kna1-name1(1) eq '3'' or

kna1-name1(1) eq '4' or

kna1-name1(1) eq '5' or

kna1-name1(1) eq '6' or

kna1-name1(1) eq '7' or

kna1-name1(1) eq '8' or

kna1-name1(1) eq '9' ).

• append somewhere

endif.

ENDSELECT.

Edited by: Bulent Balci on Jul 23, 2010 3:11 PM

[Former Member]

<div style="text-align:left">Is it possible to use Regular Expressions in select queries?</div>

As there's already quite a few discussions going on alternatives, let me come back to your original question: It is not possible to use regular expressions with [open SQL|http://help.sap.com/abapdocu_70/en/ABENOPENSQL.htm]. However, depending on your underlying database you might actually have this feature if you use [native SQL|http://help.sap.com/abapdocu_70/en/ABENNATIVESQL.htm]. E.g. Oracle introduced with 10g the condition [regexp_like|http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/conditions018.htm].

However,use of regular expressions (if available for the database you're using) with native SQL might actually not be better than a well crafted alternative without it. So as always, try different alternatives and pick the one that performs best given your optimization requirements (e.g. time, disk IO, etc.)