topic Re: SUBSTRING in Application Development and Automation Discussions

SUBSTRING

Former Member — Wed, 08 Mar 2006 14:08:20 GMT

In the select query how to search for a particular part of data. (in ABAP code)

Table : ABC

Field name : Category

Field content : Enterprise.

Query : Select * from ABC where substr(category,1,3) ='Ent'.

[The query should check the complete table ABC and list all the category where the first three characters are = 'Ent']

Re: SUBSTRING

Former Member — Wed, 08 Mar 2006 14:10:36 GMT

Vincent,

Why don't you use a like operator?

Select * from ABC where category like '%Ent%'.

Regards,

Ravi

Re: SUBSTRING

RichHeilman — Wed, 08 Mar 2006 14:11:00 GMT

Something like this.


Select * from ABC 
    where category like 'Ent%'.

regards,

Rich Heilman

Re: SUBSTRING

Former Member — Wed, 08 Mar 2006 14:12:21 GMT

Hi ,

Try this :

Select * from ABC where category LIKE 'ENT%'

Regards,

Lanka

Re: SUBSTRING

RichHeilman — Wed, 08 Mar 2006 14:14:03 GMT

Help on the subject....

<i>

Variant 3

f [NOT] LIKE g

Addition:

... ESCAPE h

Effect

The condition is met for a table entry if the statement"f (does not) equal the pattern in g" is true forthe values of f and g. f must always be afield descriptor, and g an ABAP field. If f has the value NULL, then the result ofthe check for the statement is unknown. Within a pattern, thereare two special characters:

'_' (underscore) stands for any single character.

'%' (percentage sign) stands for any sequence of characters,including an empty string.

Examples

Example to select all customers whose name begins with'M':

TABLES SCUSTOM.

SELECT ID NAME FROM SCUSTOM

INTO CORRESPONDING FIELDS OF SCUSTOM

WHERE NAME LIKE 'M%'.

WRITE: / SCUSTOM-ID, SCUSTOM-NAME.

ENDSELECT.

Example to select all customers whose name contains 'huber':

TABLES SCUSTOM.

SELECT ID NAME FROM SCUSTOM

INTO CORRESPONDING FIELDS OF SCUSTOM

WHERE NAME LIKE '%huber%'.

WRITE: / SCUSTOM-ID, SCUSTOM-NAME.

ENDSELECT.

Example to select all customers whose name does not contain 'n' as the second character:

TABLES SCUSTOM.

SELECT ID NAME FROM SCUSTOM

INTO CORRESPONDING FIELDS OF SCUSTOM

WHERE NAME NOT LIKE '_n%'.

WRITE: / SCUSTOM-ID, SCUSTOM-NAME.

ENDSELECT.

Notes

LIKE can only be used for alphanumeric databasefields. In other words, table field f must have Dictionary typeACCP, CHAR, CLNT, CUKY, LCHR,NUMC, UNIT, VARC, TIMS or DATS. Thecomparison field g must always have type C.

The maximum length of the pattern is 2n - 1 characters, where n isthe length of field f.

Trailing spaces are ignored in comparison field g. If apattern contains trailing spaces, you must enclose it in singleinverted commas ('). If your pattern is enclosed in inverted commas andyou also want to include inverted commas as part of the pattern, theinverted commas in the pattern must be doubled.

You cannot use this variant in the ON addition to theFROM clause.

</i>

Regards,

Rich Heilman

Re: SUBSTRING

Former Member — Wed, 08 Mar 2006 14:14:06 GMT

Hi vincent

do like this

select * from ABC where category like 'ENT%'

regards

kishore

Re: SUBSTRING

venkata_ramisetti — Wed, 08 Mar 2006 14:14:21 GMT

Hi,

Query : Select * from ABC where category like '%Ent%'.

More info about LIKE in the SAP documentation:

f [NOT] LIKE g

Addition:

... ESCAPE h

Effect

The condition is met for a table entry if the statement "f (does not) equal the pattern in g" is true for the values of f and g. f must always be a field descriptor, and g an ABAP field or an ABAP string. If f has the value NULL, then the result of the check for the statement is unknown. Within a pattern, there are two special characters:

'_' (underscore) stands for any single character.

'%' (percentage sign) stands for any sequence of characters, including an empty string.

Examples

Example to select all customers whose name begins with 'M':

DATA SCUSTOM_WA TYPE SCUSTOM.

SELECT ID NAME FROM SCUSTOM

INTO CORRESPONDING FIELDS OF SCUSTOM_WA

WHERE NAME LIKE 'M%'.

WRITE: / SCUSTOM_WA-ID, SCUSTOM_WA-NAME.

ENDSELECT.

Example to select all customers whose name contains 'huber':

DATA SCUSTOM_WA TYPE SCUSTOM.

SELECT ID NAME FROM SCUSTOM

INTO CORRESPONDING FIELDS OF SCUSTOM_WA

WHERE NAME LIKE '%huber%'.

WRITE: / SCUSTOM_WA-ID, SCUSTOM_WA-NAME.

ENDSELECT.

Example to select all customers whose name does not contain 'n' as the second character:

DATA SCUSTOM_WA TYPE SCUSTOM.

SELECT ID NAME FROM SCUSTOM

INTO CORRESPONDING FIELDS OF SCUSTOM_WA

WHERE NAME NOT LIKE '_n%'.

WRITE: / SCUSTOM_WA-ID, SCUSTOM_WA-NAME.

ENDSELECT.

Notes

LIKE can only be used for alphanumeric database fields. In other words, table field f must have Dictionary type ACCP, CHAR, CLNT, CUKY, LCHR, NUMC, UNIT, VARC, TIMS or DATS. The comparison field g must always have type C.

The maximum length of the pattern is 2n - 1 characters, where n is the length of field f.

Trailing spaces are ignored in comparison field g. If a pattern contains trailing spaces, you must enclose it in single inverted commas ('). If your pattern is enclosed in inverted commas and you also want to include inverted commas as part of the pattern, the inverted commas in the pattern must be doubled.

You cannot use this variant in the ON addition to the FROM clause.

Thanks,

Ramakrishna

Re: SUBSTRING

Former Member — Wed, 08 Mar 2006 14:14:55 GMT

Select * from ABC where category like 'Ent%'

Re: SUBSTRING

Former Member — Wed, 08 Mar 2006 14:17:29 GMT

Hi ,

The solution is

select * from ABC where category like 'Ent%'.

Regards,

Sylendra.

Re: SUBSTRING

Former Member — Wed, 08 Mar 2006 14:51:35 GMT

Since you want to look for 'Ent', you have to examine the domain for the field to see if it allows lower case. If it does, you have to consider if you need 'Ent%', 'ENT%' and 'ent%'. If so, you may want to consider native SQL.

Rob